IE Download File Prompt

Hi All,

I am in the process of making a Macro to automate a tedious process I go throguh every week. I have a bunch of logins in an excel file. I have to go to an internal website/database and download an Excel file for each login. It takes the first couple of days of every week to pull the files as I have about 100 or so reports I have to pull.

With help from others on this forum which is greatly appreciated. I have compiled code to retrieve the login info from the excel file, Navigate to the website, login, navigate multiple iframes, select criteria for report, and select the "Run" button.

I am now at the point where I receive a Download File prompt to Open, Save or Cancel. I need help selecting Save and then selecting a path to save the file.

I have seen the API Downloadfile code but I do not have a known URL. The end of the URL has a "#" instead of a report number or anything to identify it by.

Below is some of the code for the Run button.

Code:

---

       
<!-- 'Run' button code starts -->
<td width="8" height="28">
<table border="0" cellpadding="0" cellspacing="0"
onClick=
"document.TargetGeneralQueryForm.submitButton.value='setQueryInfo';
document.TargetGeneralQueryForm.runCriteria.value=document.TargetGeneralQueryForm.elements['run_query'][0].options[document.TargetGeneralQueryForm.elements['run_query'][0].selectedIndex].value;
document.TargetGeneralQueryForm.userAction.value='run';document.TargetGeneralQueryForm.queryID.value='-1';submitQueryFormWithDoBeforeSubmit('TargetGeneralQueryForm','SetTargetGenQueryInfo.view');return false;"
title='Run'>
<tr>

<td class="btnLeft"><img border="0" src="/tcf/common/images/Target/spacer.gif" width="10" height="1">
</td>

<td class="btnMiddle">
<a href='#' class="buttonLink" id ="run">Run</a>
</td>
                                                       
<td class="btnRight"><img border="0" src="/tcf/common/images/Target/spacer.gif" width="10" height="1"></td>

</tr>
</table>
</td>
<!-- 'Run' button code ends -->

---

I have also thought about temporarily adjusting the registry keys to change the default from cancel to save and then set the download path.

please let me know if I can provide any additional info.

you can use APIs findwindow or similar to determine when the dialog is open, then sendmessage to submit a value to it

or if you can return the value (url) of the webquery before submitting the form, you can use urldownloadtofile API

I found the code below but I am having trouble getting it to run. It was setup on windows xp. It finds the File download window and clicks save it then finds the save as prompt. Then I can not get it to recognize the child window "Edit" to change the file name.

My problem is first I can not find or do not know how to obtain the download URL. Any suggestion would be great.

Code:

---

Option Explicit
Public Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)
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 SetForegroundWindow Lib "user32" _
    (ByVal hWnd As Long) 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 SendMessageByString Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
Public Const BM_CLICK = &HF5
Public Const WM_SETTEXT = &HC
Public Const WM_GETTEXT = &HD
Public Const WM_GETTEXTLENGTH = &HE

---

Code:

---



Public Sub Test_Download()


*****my code and internal Website login info*****


    File_Download_Click_Save
       
    Save_As_Set_Filename saveInFolder, saveFilename
   
    Save_As_Click_Save
     
    Download_complete_Click_Close
   
    Debug.Print "Finished"
   
End Sub

Private Sub File_Download_Click_Save()
   
    Dim hWnd As Long
    Dim timeout As Date
   
    Debug.Print "File_Download_Click_Save"
   
    'Find the File Download window, waiting a maximum of 30 seconds for it to appear
   
    timeout = Now + TimeValue("00:00:30")
    Do
        hWnd = FindWindow("#32770", "File Download")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout
   
    Debug.Print "  File Download window "; Hex(hWnd)
   
    If hWnd Then
        'Find the child Save button
       
        hWnd = FindWindowEx(hWnd, 0, "Button", "&Save")
        Debug.Print "  Save button "; Hex(hWnd)
    End If
   
    If hWnd Then
   
        'Click the Save button
       
        SetForegroundWindow (hWnd)
        Sleep 600  'this sleep is required and 600 miiliseconds seems to be the minimum that works
        SendMessage hWnd, BM_CLICK, 0, 0
    End If
End Sub

Private Sub Test_Save_As_Set_Filename()
    'Test setting the Save As filename.  The Save As window must be displayed before running this
    Dim theFolder As String, theFilename As String
    theFolder = ThisWorkbook.Path & "\"
    theFilename = "test " & Format(Now, "hh_mm_ss") & ".csv"
    Save_As_Set_Filename theFolder, theFilename
End Sub

Private Sub Save_As_Set_Filename(folder As String, filename As String)
    'Populate the 'File name:' edit window in the Save As dialogue with the specified folder and/or filename.
    'If folder = "" a folder path is not prepended and therefore the default save folder is used.
    'If filename = "" the default file name (already populated) is used.
   
    'The Save As window has the following child window hierarchy:
   
    '  "Save As", #32770 Dialog
    '      "FileName2011_11_11_11_00_26", ComboBoxEx32    (default value in combobox)
    '          "", ComboBox
    '              "FileName2011_11_11_11_00_26"", Edit    (default value in combobox's edit box)
   
    Dim hWnd As Long
    Dim timeout As Date
    Dim fullFilename As String
   
    Debug.Print "Save_As_Set_Filename " & folder
   
    'Find the Save As window, waiting a maximum of 10 seconds for it to appear
   
    timeout = Now + TimeValue("00:00:10")
    Do
        hWnd = FindWindow("#32770", "Save As")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout
    If hWnd Then
   
        SetForegroundWindow (hWnd)
       
        'Find the child ComboBoxEx32 window
       
        hWnd = FindWindowEx(hWnd, 0, "ComboBoxEx32", vbNullString)
        Debug.Print "  ComboBoxEx32 "; Hex(hWnd)
    End If
   
    If hWnd Then
   
        'Find the child ComboBox window
       
        hWnd = FindWindowEx(hWnd, 0, "ComboBox", "")
        Debug.Print "  ComboBox "; Hex(hWnd)
    End If
               
    If hWnd Then
       
        SetForegroundWindow (hWnd)
        'Find the child Edit window
       
        hWnd = FindWindowEx(hWnd, 0, "Edit", "")
        Debug.Print "  Edit "; Hex(hWnd)
    End If
   
    If hWnd Then
           
        If filename = "" Then
            'Get default filename (already populated in Edit window)
            filename = Get_Window_Text(hWnd)
        End If
     
        If folder <> "" And Right(folder, 1) <> "\" Then folder = folder & "\"  'if specified, ensure folder ends with \
       
        fullFilename = folder & filename
        Debug.Print "Full filename " & fullFilename
       
        'Populate the Edit window with the full file name
       
        Sleep 200
        SendMessageByString hWnd, WM_SETTEXT, Len(fullFilename), fullFilename
    End If
   
End Sub

Private Function Get_Window_Text(hWnd As Long) As String
    'Returns the text in the specified window
   
    Dim buffer As String
    Dim length As Long
    Dim result As Long
   
    length = SendMessage(hWnd, WM_GETTEXTLENGTH, 0, 0)
    buffer = Space(length + 1)  '+1 for the null terminator
    result = SendMessage(hWnd, WM_GETTEXT, Len(buffer), ByVal buffer)
    Debug.Print "Edit File name = " & Left(buffer, length)
    Debug.Print "    length = " & length
   
    Get_Window_Text = Left(buffer, length)
   
End Function

Private Sub Save_As_Click_Save()
    'Click the Save button in the Save As dialogue
   
    Dim hWnd As Long
    Dim timeout As Date
   
    Debug.Print "Save_As_Click_Save"
    'Find the Save As window, waiting a maximum of 10 seconds for it to appear
   
    timeout = Now + TimeValue("00:00:10")
    Do
        hWnd = FindWindow(vbNullString, "Save As")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout
    If hWnd Then
   
        SetForegroundWindow (hWnd)
           
        'Get the child Save button
       
        hWnd = FindWindowEx(hWnd, 0, "Button", "&Save")
        Debug.Print "  Save button "; hWnd
    End If
   
    If hWnd Then
        'Click the Save button
       
        SendMessage hWnd, BM_CLICK, 0, 0

    End If
       
End Sub

Private Sub Download_complete_Click_Close()
   
    Dim hWnd As Long
    Dim timeout As Date
   
    Debug.Print "Download_complete_Click_Close"
       
    'Find the Download complete window, waiting a maximum of 30 seconds for it to appear.  Timeout value is dependent on the
    'size of the download, so make it longer for bigger files
   
    timeout = Now + TimeValue("00:00:30")

    Do
        hWnd = FindWindow("#32770", "Download complete")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout
 
    Debug.Print "  Download complete window "; Hex(hWnd)
   
    If hWnd Then
        'Find the child Close button
       
        hWnd = FindWindowEx(hWnd, 0, "Button", "Close")
        Debug.Print "  Close button "; Hex(hWnd)
    End If
   
    If hWnd Then
   
        'Click the Close button
       
        SetForegroundWindow (hWnd)

        Sleep 600  'this sleep is required and 600 miiliseconds seems to be the minimum that works

        SendMessage hWnd, BM_CLICK, 0, 0

    End If
 
End SubThis goes in Module2:

Code:

---

I also get a type mismatch error on the following line: Save_As_Set_Filename saveInFolder, saveFilename. Is you can tell I am fairly new to coding. For me this is somewhat complex and all the guidence I get is very much appreciated.

i would love to be able to use the urldownloadtofile but my problem is that I dont know how to determine the URL.....


The site/database that I have to go to first requires me to login then fill out a bunch of info. Then I click a button that says "RUN" which runs the javascript that I have above... The URL in the URL bar stays the same throughout the whole process not matter what I select. Once I hit the run button the File Download dialog box pops up.

How do I determine the URL that has prompted the file download box?

Forgive me but i dont know how to use APIs and I have been searching the internet for almost 2 weeks now. Ha, my frustration comes from this is literally the last step.

Please Help

Hi sskicker23,

Thanks for your posts, its been the only place I could find this to help me with a similar issue.

I've borrowed elements of your code and it works perfectly for me. Your whole code is not posted but I suspect you have an issue either in your

vb Code:

Dim saveInFolder

or

vb Code:

Dim saveFilename

.

If you're not declared these correctly then they could be assuming an incorect variable type due to the values you're giving them (I presume from data in your worksheet?).

Good luck anfd let me know if I can help.

M

could you guys post a working code that clicks save automatically ?

vb Code:

Public Sub File_Download_Click_Save(Optional cmb As String)
    
    Dim hWnd As Long
    Dim timeout As Date
    
    'Debug.Print "File_Download_Click_Save"
    
    'Find the File Download window, waiting a maximum of 30 seconds for it to appear
    
    timeout = Now + TimeValue("00:00:30")
    Do
        hWnd = FindWindow("#32770", "File Download")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout
    
    'Debug.Print "   File Download window "; Hex(hWnd)
    
    If hWnd Then
        'Find the child Save button
        
        hWnd = FindWindowEx(hWnd, 0, "Button", "&Save")
        'Debug.Print "   Save button "; Hex(hWnd)
    End If
    
    If hWnd Then
    
        'Click the Save button
        
        SetForegroundWindow (hWnd)
        Sleep 600  'this sleep is required and 600 miiliseconds seems to be the minimum that works
        SendMessage hWnd, BM_CLICK, 0, 0
    End If
End Sub

Works for me,

M

thanks mawdo. May i know why we have to wait 30 seconds ? as far as i can see the codes assumes the file download windows is already visable!

May i know why findwindow is inside "do loop untill" ? it would be nice if you explain it to me.Furthermore more is it possible to click both save and save as buttons and click download button inside one function? How?(For example how i can make the function so it clicks the download button then waits for it to pop up and then clicks both save and save as buttons ?)

Note: i am looking for a vb6 solution!

Code:

---

Do
        hWnd = FindWindow("#32770", "File Download")
        DoEvents
        Sleep 200
    Loop Until hWnd Or Now > timeout

---

Quote:

---

as far as i can see the codes assumes the file download windows is already visable!

---

probably not, i would assume this code is called immediately on clicking the download button, so the dialog would not be visible yet

Quote:

---

pop up and then clicks both save and save as buttons ?

---

i do not believe this would be possible, anymore than you can click both buttons manually, only one would work
also if you click the saveAs a further dialog would show to ask for a filepath\name, which you would also have to findwindow and sendmessage
to do both you would have to click the download button again after processing the first request

Thanks guys for reply. Mawdo why your code will not work if the download button is clicked from webrowser control that is in same project as your code(i added small code at start of your code that clicks download button inside webbrowser control) ?The File_Download_Click_Save button is cant be clicked when file download pops up!!the project form is locked so i can't click the button!is there a way to fix this ?

Quote:

---

the project form is locked so i can't click the button!is there a way to fix this ?

---

you are not giving enough information as to what is happening, we can only assume that the code is staying within a loop or something, not able to find the correct window
you have not posted your own code, so we can not know what changes you may have made

as you are using a webbrowser control there may be some event that you can have that will avoid having to wait til the dialog has loaded

Hey Tony, is the javascript in the forms the only thing that is keeping you from using a different method? Is the javascript so complex that what it does would be real cumbersome to replicate? The reason I'm asking is that findwindow and sendmessage with the save as dialog is kludgy. It may get you going at first, but will cause you more problems in the long run. There's no guarantee that the dialog will appear when you submit, MS might break this by altering how IE behaves in the future, a mysterious dialog message could pop up from IE before the save dialog and "freeze" IE, etc.

If possible I would suggest using WinHttpRequest wherever possible.

http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx