Announcement

Collapse
No announcement yet.

Call UserForm to Re-enter Password when Error logging in

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Call UserForm to Re-enter Password when Error logging in



    I've written code to gain access to a secured site with my username and password via a UserForm (for an added security feature), and it works perfectly! My question to you guys is...

    If the username or password are entered incorrectly in the UserForm and brings back a sign on error webpage, how can I re-call the UserForm to re-enter the required information?


    Here is the part of the code regarding the IE navigating and signing on:
    Code:
    Private Sub IE_Navigate_and_Download(URL As String, saveInFolder As String, saveAsFilename As String)
    
    
    'Fill in UserForm to submit username and Password for website
      WRSlogin.Show
    
    
        
    'Use existing IE window or open a new IE window
            
      Dim IE As Object
      
      With CreateObject("Shell.Application").Windows
        
        If .Count > 0 Then
          ' Get IE
          Set IE = .Item(0) ' or .Item(.Count - 1)
        Else
          ' Create IE
          Set IE = CreateObject("InternetExplorer.Application")
          IE.Visible = True
        End If
     End With
           
       
        With IE
            .Visible = True
            .navigate URL
            While .busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
        End With
        
        Application.Wait (Now + TimeValue("0:00:02"))
        
    '....................
         
    'Access with Username and Password.
    
    
      Dim doc As Object    'HTMLDocument
         
            Set doc = IE.Document
     
            doc.getElementById("Username ").Value = Range("BA1").Value
            doc.getElementById("password").Value = Range("BA2").Value
            doc.getElementById("countryLocation").SelectedIndex = "1"
            doc.getElementById("submit").Click
            
            Do While IE.busy: DoEvents: Loop
            Do While IE.ReadyState <> 4: DoEvents: Loop
     
         '///////////////////
         'There is where I don't know how to call the userform
         'if I get a login error.
         
    
    
        Set IE = Nothing
    
    End Sub
    The only change on the webpage is that it returns the original webpage with the following sign-on error:

    <span style="font-weight: bold; color: red;">Sign In Error:</span>


    Thank you!
    Hilary

  • #2
    Re: Call UserForm to Re-enter Password when Error logging in

    will the URL in the address bar not be different if access is denied? If so you could check the URL to see if the login worked, if not - then call the userform again.

    You could wrap this whole sub in a Loop, here's a crude example:

    Code:
    Do
    UserForm1.Show
    '//Existing code here
    Loop While Not IE.LocationURL = "https://access.granted.myurl.com/"
    Also, for future reference, please remove any format tags from text before wrapping it in CODE tags as this will cause it to display incorrectly. (Like your final comment block)

    Comment


    • #3
      Re: Call UserForm to Re-enter Password when Error logging in

      Originally posted by S O View Post
      will the URL in the address bar not be different if access is denied? If so you could check the URL to see if the login worked, if not - then call the userform again.
      Thank you for your reply, S O!

      No, the URL is the same whether you are at the main page, denied access, or even if access is granted.
      I just tried various methods with your code, but I can't get it to work. Is there a way to look for script on the page?

      Comment


      • #4
        Re: Call UserForm to Re-enter Password when Error logging in

        It might be that it displays the result in an iFrame or something then - can you provide the link to the page? I would have to see what gets returned after the form is submitted.

        When you say "look for script"... do you mean HTML source code / javascript / PHP ???

        Comment


        • #5
          Re: Call UserForm to Re-enter Password when Error logging in

          Originally posted by S O View Post
          It might be that it displays the result in an iFrame or something then - can you provide the link to the page? I would have to see what gets returned after the form is submitted.

          When you say "look for script"... do you mean HTML source code / javascript / PHP ???

          Unfortunately, it's an internal website that's firewalled.

          The source code is written with CSS. The sign in error is associated with the following properties.
          span
          • id: ""
          • innerHTML: "Sign In Error:"
          • innerText: "Sign In Error:"
          • isContentEditable: false
          • lang: ""
          • lastChild: text
          • lastElementChild: null
          • localName: "span"
          • namespaceURI: "http://www.w3.org/1999/xhtml"
          • nextElementSibling: null
          • nextSibling: text
          • nodeName: "SPAN"
          • nodeType: 1
          • nodeValue: null
          • offsetHeight: 15
          • offsetLeft: 146
          • offsetParent: td
          • offsetTop: 12
          • offsetWidth: 86
          • onabort: null
          • onautocomplete: null
          • outerHTML: "<span style="font-weight: bold; color: red;">Sign In Error:</span>"
          • outerText: "Sign In Error:"
          • ownerDocument: document
          • parentElement: div
          • parentNode: div
          • prefix: null
          • previousElementSibling: img
          • previousSibling: text
          • scrollHeight: 0
          • scrollLeft: 0
          • scrollTop: 0
          • scrollWidth: 0
          • shadowRoot: null
          • spellcheck: true
          • style: CSSStyleDeclaration
          • tabIndex: -1
          • tagName: "SPAN"
          • textContent: "Sign In Error:"
          • title: ""
          • translate: true
          • webkitdropzone: ""



          I try to call the error with the following, but it brings back the error that the Object doesn't support this property or method, calling back the second line there:
          Code:
          Dim strTD As String
          strTD = doc.getElementByTagName("Span").innerText
          MsgBox strTD
          I've selected the IE and HTML references, but now I don't know what else it could be.

          Comment


          • #6
            Re: Call UserForm to Re-enter Password when Error logging in

            FYI - CSS is just for styling HTML code, but that tells me that we're working with HTML source code which is much easier

            The <span> tag is a generic tag, it doesn't have any individual identity so to speak, there could be 100 <span> tags in a page's source code and so trying to get this element is asking for a mistake to happen.

            It sounds like we're working with a PHP document rather than a HTML document, which makes things a bit trickier but the following might work for getting you past the login page.

            Code:
            Private Sub IE_Navigate_and_Download(URL As String, saveInFolder As String, saveAsFilename As String) 
                 
                 
                 'Fill in UserForm to submit username and Password for website
              Do WRSlogin.Show 
                 
                 
                 
                 'Use existing IE window or open a new IE window
                 
                Dim IE As Object
                Dim x
                Dim LogIn As Boolean
                 
                With CreateObject("Shell.Application").Windows 
                     
                    If .Count > 0 Then 
                         ' Get IE
                        Set IE = .Item(0) ' or .Item(.Count - 1)
                    Else 
                         ' Create IE
                        Set IE = CreateObject("InternetExplorer.Application") 
                        IE.Visible = True 
                    End If 
                End With 
                 
                 
                With IE 
                    .Visible = True 
                    .navigate URL 
                    While .busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend 
                    End With 
                     
                    Application.Wait (Now + TimeValue("0:00:02")) 
                     
                     '....................
                     
                     'Access with Username and Password.
                     
                     
                    Dim doc As Object 'HTMLDocument
                     
                    Set doc = IE.Document 
                     
                    doc.getElementById("Username ").Value = Range("BA1").Value 
                    doc.getElementById("password").Value = Range("BA2").Value 
                    doc.getElementById("countryLocation").SelectedIndex = "1" 
                    doc.getElementById("submit").Click 
                     
                    Do While IE.busy: DoEvents: Loop 
                        Do While IE.ReadyState <> 4: DoEvents: Loop 
                             
            On Error Resume Next
                            Set x = doc.getElementById("Username")
                            
            If x Is Nothing Then: LogIn = True: Else: LogIn = False
            
            Err.Clear
            On Error GoTo 0
                             
                   Loop Until LogIn = True          
                             
                            Set IE = Nothing 
                             
                        End Sub

            Comment


            • #7
              Re: Call UserForm to Re-enter Password when Error logging in

              Oh, it works perfectly!! The only thing I had to change was move the UserForm after 'Do' to a second line.

              I'm still a novice when it comes to coding, so thank you for all your help!

              Comment


              • #8
                Re: Call UserForm to Re-enter Password when Error logging in

                Another question for you...

                Once I'm inside the webpage, my goal is to download an excel spreadsheet. The new URL link automatically downloads the file on my desktop. Is there a way to change the name and path before it's saved?

                Comment


                • #9
                  Re: Call UserForm to Re-enter Password when Error logging in

                  You might actually be able to use this directly without logging in if you provide your username and password:

                  Code:
                  Sub SO()
                  
                   Dim myURL As String, fSave As String, Username As String, Password As String
                  
                   Username = "user123"
                   Password = "Pass123"
                  
                   myURL = "http://www.myurl.com/download?=workbook.xls" '//Change as required
                   fSave = "C:\Users\myUser\Desktop\Folder\NewWorkbook.xls" '//Change as required **make sure you give it a full path including a filename**
                  
                  
                      Dim WinHttpReq As Object, oStream As Object
                      Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
                      WinHttpReq.Open "GET", myURL, False, Username, Password
                      WinHttpReq.Send
                     
                      myURL = WinHttpReq.ResponseBody
                      If WinHttpReq.Status = 200 Then
                          Set oStream = CreateObject("ADODB.Stream")
                          oStream.Open
                          oStream.Type = 1
                          oStream.Write WinHttpReq.ResponseBody
                          oStream.SaveToFile fSave, 2  '// 1 = no overwrite, 2 = overwrite
                         oStream.Close
                      End If
                  
                      Set WinHttpReq = Nothing
                  
                  End Sub

                  Comment


                  • #10
                    Re: Call UserForm to Re-enter Password when Error logging in

                    Originally posted by S O View Post
                    You might actually be able to use this directly without logging in if you provide your username and password
                    There will be multiple users using this workbook, so the UserForm is so each one can log in separately. I'll try adjusting my code to see if I can get parts of your code to work for my needs.

                    Thanks!

                    Comment


                    • #11
                      Re: Call UserForm to Re-enter Password when Error logging in

                      Just take the username/password section out and use it after you've logged in then:
                      Code:
                      Sub SO() 
                           
                          Dim myURL As String, fSave As String, Username As String, Password As String 
                      
                          myURL = "http://www.myurl.com/download?=workbook.xls" '//Change as required
                          fSave = "C:\Users\myUser\Desktop\Folder\NewWorkbook.xls" '//Change as required **make sure you give it a full path including a filename**
                           
                           
                          Dim WinHttpReq As Object, oStream As Object 
                          Set WinHttpReq = CreateObject("Microsoft.XMLHTTP") 
                          WinHttpReq.Open "GET", myURL, False
                          WinHttpReq.Send 
                           
                          myURL = WinHttpReq.ResponseBody 
                          If WinHttpReq.Status = 200 Then 
                              Set oStream = CreateObject("ADODB.Stream") 
                              oStream.Open 
                              oStream.Type = 1 
                              oStream.Write WinHttpReq.ResponseBody 
                              oStream.SaveToFile fSave, 2 '// 1 = no overwrite, 2 = overwrite
                              oStream.Close 
                          End If 
                           
                          Set WinHttpReq = Nothing 
                           
                      End Sub

                      Comment


                      • #12
                        Re: Call UserForm to Re-enter Password when Error logging in

                        I tried your code, but there was an issue with the saved file being the information from a pop-up window that's automatically opened and closed before I get to the login screen. I'm not sure how to get around that.

                        But by taking out the username and password, the user would have to enter it directly into the webpage?

                        Comment


                        • #13
                          Re: Call UserForm to Re-enter Password when Error logging in

                          Okay, let me try and follow the logic here - the file that gets downloaded appears before you log into the webpage? And it's in a pop-up window?

                          Is the file always called the same thing by any chance? or is there some kind of variable in the name (i.e. a date)?

                          Comment


                          • #14
                            Re: Call UserForm to Re-enter Password when Error logging in

                            No, when the login page loads, it creates a pop-up window that closes itself before it finishes loading. Then once you log in, you can search on a second page that will allow you to download the results to Excel. There are two different searches that I actually want to download the results from, and the URL's for the two searches/spreadsheets are different. When the file tries to automatically download, it spits it out with the same name, which is another reason for trying to change the name.

                            Comment


                            • #15


                              Re: Call UserForm to Re-enter Password when Error logging in

                              After a lot of research and trial and error, I finally got my code to work!

                              Code:
                              Public Sub SaveFilesFromURL()
                              
                              
                                
                                  Dim mainURL As String, xxxURL As String, yyyURL As String, fSaveXXX As String, fSaveYYY As String
                                   
                                  mainURL = "https://main  
                                  xxxURL = "https://xxx 
                                  yyyURL = "https://yyy
                                  fSaveXXX = "C:\Users\... .xls" '//Change as required **make sure you give it a full path including a filename**
                                  fSaveYYY = "C:\Users\... .xls"
                                   
                              'Fill in UserForm to submit Username and Password for main website
                                  Do
                                  UserForm.Show
                                  
                              'Log into main URL
                                  Username= Range("BA1").Value
                                  Password = Range("BA2").Value
                                  strAuthenticate = "username=" & Username & "&password=" & Password & "&countryLocation=US" & "&submit=+working...+" & "&jsUTCOffset=420"     '&jsUTCOffset=420 sets the time zone for my location.
                              
                              
                                  Dim WHTTP As Object
                                  Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
                              
                              
                                  WHTTP.Open "POST", mainURL, False
                                  WHTTP.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"
                                  WHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.104 Safari/537.36"
                                  WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
                                  WHTTP.send strAuthenticate
                                  
                                  
                              'To determine if logged in correctly, the cookies must be looked at.  This could be different for any page though.  WHTTP.getAllResponseHeaders is good for looking this up.
                              
                              Dim strHeaders, hArr, kk, theCookie
                                   strHeaders = WHTTP.getAllResponseHeaders
                                   hArr = Split(strHeaders, vbCrLf)
                                   Dim t, p, firstpos
                                   For kk = 0 To UBound(hArr) - 1
                                    t = hArr(kk)
                                    If Mid(t, 1, Len("Set-Cookie: ")) = "Set-Cookie: " Then
                                       firstpos = Len(t)
                                       p = InStr(1, t, "CFID=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "CFTOKEN=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "JSESSIONID=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; Path=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; Domain=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; Max-Age=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; Secure=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; Version=", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       p = InStr(1, t, "; HTTPOnly", vbTextCompare): If p > 0 And p < firstpos Then firstpos = p
                                       theCookie = Mid(t, Len("Set-Cookie: ") + 1, firstpos - 1 - Len("Set-Cookie: "))
                                       If cookie = "" Then
                                        cookie = theCookie
                                       Else
                                        cookie = cookie & "; " & theCookie
                                       End If
                                     End If
                                    Next
                                    cookie = Trim(cookie)
                                 
                                     
                              'If EID or password are incorrect, re-enter information in UserForm.
                                  Dim LogIn As Boolean
                                  
                                  On Error Resume Next
                              
                              
                                      If cookie = "" Then: LogIn = False: Else: LogIn = True
                              
                              
                                      Err.Clear
                                      On Error GoTo 0
                                      
                                  Loop Until LogIn = True
                              
                              
                              
                              
                              
                              
                              'Then GET direct xxx file url
                                  WHTTP.Open "GET", xxxURL, False
                                  WHTTP.send
                              
                              
                              'Save the XXX file
                                  Dim oStream As Object
                                   xxxURL = WHTTP.responseBody
                                  If WHTTP.Status = 200 Then
                                      Set oStream = CreateObject("ADODB.Stream")
                                      oStream.Open
                                      oStream.Type = 1
                                      oStream.Write WHTTP.responseBody
                                      oStream.SaveToFile fSaveXXX, 2 '// 1 = no overwrite, 2 = overwrite
                                      oStream.Close
                                  End If
                              
                              
                              
                              
                              'Then GET direct yyy file url
                                  WHTTP.Open "GET", yyyURL, False
                                  WHTTP.send
                              
                              
                              'Save the YYY file
                                  yyyURL = WHTTP.responseBody
                                  If WHTTP.Status = 200 Then
                                      Set oStream = CreateObject("ADODB.Stream")
                                      oStream.Open
                                      oStream.Type = 1
                                      oStream.Write WHTTP.responseBody
                                      oStream.SaveToFile fSaveYYY, 2 '// 1 = no overwrite, 2 = overwrite
                                      oStream.Close
                                  End If
                              
                              
                              
                              
                                  Set WHTTP = Nothing
                                   
                              
                                  MsgBox "Files have been saved!", vbInformation, "Success"
                                      
                              End Sub

                              Comment

                              Working...
                              X