none
VBA How to download a generated file from IE RRS feed

  • Question

  • Hi guys,

    Apologies if this is an easy one, I couldn't find anything that helped me with my problem and was hoping someone might point me in the right direction for which object/argument to use.

    I'm trying to automate the downloading of a report, it will be a CSV file however it's not a static address (i.e. www.hello.com/files/goodbye.csv)

    The website is a CMS with database backend and upon clicking a button, the report is generated and the user will be prompted to download a resulting file (once it has been prepared).

    It is this action that I am trying to automate, however I'm not sure how to get IE to click the button and automatically saving instead of prompting the user at all (ideally this will be done with IE Visible being false in the end).

    Any help would be greatly appreciated!

    Best regards,

    Jeff.




    • Edited by Jeff simply Monday, February 16, 2015 3:29 PM
    Monday, February 16, 2015 11:21 AM

All replies

  • Hi Jeff,

    In order to click the button you will need the ID of the button element, so probably you will need the  getElementById method:

    https://developer.mozilla.org/en-US/docs/Web/API/document.getElementById

    Last month I wrote a A VBA code for logging-in to a website. The code uses getElementById method in order to find the username and password textboxes, as well as the log-in button. Here is the code:

    http://www.myengineeringworld.net/2015/01/website-log-in-automation-vba-macro.html

    It might give you some ideas on how to apply this method for your problem.

    Best Regards,

    Christos

    Monday, February 16, 2015 3:49 PM
  • Hi Christos,

    Thanks for your reply.

    Yes I've got as far as logging into the site, navigating to the appropriate page, however one there I'm stuck with the command I need to use...

    The website in question does not always have an ID for buttons. In this case it does so I can say:

    csvButton = doc.GetElementById("id_root_2_2_5_5")
    csvButton.Click

    However this is the point at which the report is generated by the back end and, assuming it's successful, will prompt the user to download. It's this part that I'm trying to capture in IE, but I've not come across any resource that explains if this is possible (although I'm telling myself it is possible, since anything is!).

    I guess I'd be looking for a state of IE that suggests a prompt of some sort, however it's this I've had big issues with in the past. I can't even really get away with SendKeys since the download dialogue initially is that yellow bar prompt at the bottom of the screen for IE.

    Best regards,

    Jeff.


    • Edited by Jeff simply Monday, February 16, 2015 4:39 PM
    Monday, February 16, 2015 4:37 PM
  • This is how I do file creation on my websites.  I send an Ajax request to the server.  It creates the file and sends back the location of the file (URL).  My Javascript creates a link to the file <a> and then clicks the link.  Once that happens the browser takes over and prompts the user to save the file.  As far as I know you have no control over the browser saving the file.  It would be a security issue.  However, you should be able to find the link with VBA and extract the location of the file.  Since this happens asynchronously you will have to loop until the link appears.  There are numerous command line tools you can call from VBA using Win32 commands.  I have used Wget and there is URL2FILE.  You put in the URL and the file save location.

    As an after thought I am not sure once the browser prompts the user will it act modal and suspend VBA execution until the user responds.

    • Edited by mogulman52 Tuesday, February 17, 2015 1:48 PM
    Tuesday, February 17, 2015 1:39 PM
  • Thanks mate,

    I would guess something similar is happening.

    I am looking at the JS being activated when the button is pressed, can you give any hints as to how the <a> would be created?

    Tuesday, February 17, 2015 3:11 PM
  • I use jquery with Javascript so the link is created like this:

        var doclink = $('<a>', {
            id: 'id123',
            text: 'Link to doc',
            title: 'shows on hover',
            href: 'docs/something.csv'
        }).appendTo('body');

    It is attached to the document body. 

     <a href="docs/something.csv" id="id123" title="shows on hover" style="visibility: hidden">Link to Doc</a>

    It might be hidden since the Javascript might click on the link.  You can use a tool like Firebug to look at the HTML and find the link.  IE has its own development tools I just like Firebug.

    Tuesday, February 17, 2015 4:23 PM
  • Can you work with this?

    Declare Function URLDownloadToFileA Lib "urlmon" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long
    
    Sub ExampleDownload()
    Dim IExpl As Object
    Set IExpl = CreateObject("InternetExplorer.Application")
    With IExpl
    .Navigate " https://www.bom.mu/?id=80277 " 'You need to change this for a variable and loop
    Do Until .Readystate = 4: Loop ' Allow page to load
    'Code below to find correct href link in page based on text
    For Each lnk In IExpl.Application.Document.Links
    If lnk.outertext = "Click Here to Open or Right Click to Download." Then Exit For
    Debug.Print lnk.outertext
    Next
    End With
    SuccessfulDownload = URLDownloadToFileA(0, lnk.href, "C:\myfilename.pdf", 0, 0)
    Set IExpl = Nothing
    End Sub
    
    
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 20, 2015 4:38 PM
  • You can use an API to do the work too!

    Option Explicit
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
         ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    
    Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
         ByVal hWnd1 As Long, ByVal hwnd2 As Long, _
         ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
     Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
                         ByVal hWnd As Long, _
                         ByVal wMsg As Long, _
                         ByVal wParam As Long, _
                         lParam As Any) As Long
    
    Public Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA"
    ( _
                         ByVal hWnd As Long, _
                         ByVal wMsg As Long, _
                         ByVal wParam As Long, _
                         ByVal lParam As Long) As Long
    
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As
    Long) As Long
    
    Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
    
    Public Const TCM_SETCURFOCUS As Long = &H1330&
    Public Const BM_CLICK As Long = &HF5&
    Private Const WM_COMMAND As Long = &H111
    
    Sub Download()
    Dim i As Long, res As Long
    Dim hWndUI As Long, hWndBtn As Long
    Dim timeOut As Double
    Const cClsName As String = "#32770" ' same classname for all the control 
    windows we'll need
            ReDim aWinText(1 To 3) As String
         aWinText(1) = "File Download - Security Warning"
         aWinText(2) = "Save As"
         aWinText(3) = "Download complete"
            ' 1,    get the File download window and click Save
         ' 2,    get the Save As window and click Save
            For i = 1 To 2
                    hWndUI = 0
                 timeOut = Timer + 5
                 While hWndUI = 0 And Timer < timeOut
                         Sleep 100&
                         hWndUI = FindWindow(cClsName, aWinText(i))
                 Wend
                    If hWndUI = 0 Then
                         ' eg bad url
                         Err.Raise 12345, , "didn't get " & vbCr & aWinText(i)
                 End If
                    SetForegroundWindow hWndUI
                    Sleep 100&
                    hWndBtn = FindWindowEx(hWndUI, 0&, "Button", "&Save")
                 Sleep 200&
                    res = SendMessage(hWndBtn, TCM_SETCURFOCUS, 1, ByVal 0&)
                 'res = SendMessage(hWndBtn, BM_CLICK, ByVal 0&, ByVal 0&)
                 res = PostMessage(hWndBtn, BM_CLICK, ByVal 0&, ByVal 0&)
                 res = SendMessage(hWndBtn, WM_COMMAND, 0&, 0&)
            Next
            ' 3, wait until Download complete appears
         hWndUI = 0
         timeOut = Timer + 10    ' increase timeOut with bigger files
         While hWndUI = 0 And Timer < timeOut
                 hWndUI = FindWindow(cClsName, aWinText(3))
         Wend
            ' optional open folder
         hWndBtn = FindWindowEx(hWndUI, 0&, "Button", "Open &Folder")
            res = SendMessage(hWndBtn, TCM_SETCURFOCUS, 1, ByVal 0&)
         res = PostMessage(hWndBtn, BM_CLICK, ByVal 0&, ByVal 0&)
        ' res = SendMessage(hWndBtn, BM_CLICK, ByVal 0&, ByVal 0&)
         res = SendMessage(hWndBtn, WM_COMMAND, 0&, 0&)
            DoEvents
    
    End Sub
     Sub Test1()
    Dim url As String
    Dim objIE As Object
            On Error GoTo errH
         url = "http://social.microsoft.com/Forums/getfile/25241/"
            ' iso IE suggest add a WebBrowser control to a sheet or a userform
         ' and use that rather than starting an instance of IE
            Set objIE = CreateObject("internetexplorer.application")
            objIE.Navigate url
            Download
    done:
         On Error Resume Next
         objIE.Quit
            Exit Sub
    
    errH:
         MsgBox Err.Description
    
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 20, 2015 4:44 PM
  • Sorry for the amazingly slow reply.

    I couldn't get this code working, I am wondering whether this was meant for IE8 and earlier?

    Checking for Window text that you do seems to suggest you expect a Dialogue box when clicking a download link, however in IE9 (at least mine does) has a notification bar at the bottom of IE.

    Thinking about it, I will now go searching for whether it's possible to check for status of that bar in VBA. Otherwise I'm out of ideas. I have noticed that using IE dev tools, shows the file I am downloading (it's a csv text file) appears in full in the response of my request. Therefore I'm wondering whether I can tackle the problem using another method of Internet integration, however I haven't done this before so I have given up for now.

    Thank you for your high effort post all the same! I won't mark it as answer just for the reason I don't want people to read this thread in the hope of an answer to the original request. But if I could give you effort points I would!!

    Tuesday, March 24, 2015 12:17 PM
  • Hi Jeff,

    I am hoping you still monitor this thread even though it's a few months old now.

    I have exactly the same need (to bypass the "Open", "Save" ,"Cancel" options on the download pseudo-window that appears in the bottom of IE11 in response to a server-generated dynamic datafile) and have it simply always "Save", and despite hours of research I have been unable to find a working solution.

    Did you ever find a way to address the download "window bar" ? Could you get a Window Handle for it ?  Or is there some other way to simply send keystrokes to it ?  (I found a solution online that depends on VB routines and sends keystrokes to the window but I am looking for a solution that works in VBA...)

    If you did ultimately find a reply, then I'd be very interested in it.

    Thanks,

    Warren K.

    Monday, June 29, 2015 6:18 AM