0

im writing a macro to try and automate a very tedious monthly task, it involves logging into a website, downloading a CSV file and then doing some cleaning and rearranging before uploading it to a database.

Im stuck on one very specific part of the process and have tried multiple solutions but cant get it to work, specifically identifying and clicking the button which creates the download file,

This is the HTML for the button I need to access -

<button class="btn btn-primary btn-xs" type="button" data-toggle="modal" data-target="#download_modal" data-licenceno="33872" data-dfname="Care &amp; Nursing Homes Master" data-dfid="1001">Create a new file containing all records</button>

This is what I have so far, it works up until the for each loop in the download file sub, i'm currently just trying to identify the button so I can click it, but it doesn't identify anything with that class, despite copying the class name exactly from the HTML, no errors but also nothing writes to the immediate window, I cant seem to find any way to access the button so any help would be appreciated.

Option Explicit
Sub Opensite()
'open caredata URL and login
    Dim IEapp As Object, WebURL As String, HTMLdoc As HTMLDocument
        Set IEapp = CreateObject("InternetExplorer.Application")
        WebURL = "URL"
    With IEapp
        .silent = True
        .Visible = True
        .Navigate WebURL
        Do Until IEapp.readyState = 4
            DoEvents
        Loop
        Set HTMLdoc = IEapp.document
    End With
    With HTMLdoc.forms(0)
        .UserName.Value = "myusername"
        .Password.Value = "mypassword"
        .btn_signin.Click
    End With
    downloadfile IEapp, HTMLdoc
End Sub
Private Sub downloadfile(IE As Object, HTML_doc As HTMLDocument)
'download CSV file
    Dim o As Object, i As Object
    Do Until IE.readyState = 4
        DoEvents
    Loop
    Set o = IE.document.getElementsByClassName("btn btn-primary btn-xs")
    For Each i In o
        Debug.Print i.innerHTML
    Next i
End Sub
b3s1m0t7
  • 3
  • 1
  • I replicated your functions using the SO downvote button as my target button and was able to print the inner HTML fine. Perhaps try navigating to the website in Chrome and inspecting element to ensure that the class name is still the same and/or doesn't change with Javascript or jQuery? – jclasley Jan 28 '20 at 13:42
  • Apologies could you elaborate please, I opened the page in chrome and inspected the element but the HTML is identical. How do I find out if it changes with Javascript or jQuery? – b3s1m0t7 Jan 28 '20 at 16:36
  • I was thinking that it may have changed when the page was loaded, but because you opened it and inspected and saw the HTML was the same, that nullifies my theory. I just tried it again with another webpage and still was able to return the innerHTML. Have you tried stepping into the code and seeing what `o` returns? – jclasley Jan 28 '20 at 16:50
  • @jclasley Completely lost now, when stepping through the code line by line the innerHTML gets printed, this doesn't happen when I just run the macro, what could cause this? EDIT - Also running `If i.innerHTML = "Create a new file containing all records" Then i.Click End If` and stepping through the code works but running the macro doesn't. – b3s1m0t7 Jan 28 '20 at 17:18
  • I have a feeling the issue is which webpage is active, could it be because the sub is targeting the log in page which doesn't have any buttons with that class name? – b3s1m0t7 Jan 28 '20 at 17:29
  • Yep, 100% you found the error. I'll explain further in an answer. – jclasley Jan 28 '20 at 17:42

1 Answers1

0

What's happening is that IE is hanging on the load, and then your o is set to nothing. When you step through it, you're giving it time to load, but when it's running automatically it doesn't have the luxury of the time difference. Even though you're waiting for the right response type, it's still loading in some back-end stuff.

You can still continue to use the IE application to click the buttons and log-in, but see this answer for the best way to get your innerHTML once you've logged in.

jclasley
  • 668
  • 5
  • 14