Results 1 to 12 of 12

Thread: IE Download File Prompt

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    11

    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.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: IE Download File Prompt

    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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    11

    Re: IE Download File Prompt

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    11

    Re: IE Download File Prompt

    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

  5. #5
    New Member
    Join Date
    Feb 2011
    Posts
    7

    Re: IE Download File Prompt

    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:
    1. Dim saveInFolder
    or
    vb Code:
    1. 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

  6. #6
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Re: IE Download File Prompt

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

  7. #7
    New Member
    Join Date
    Feb 2011
    Posts
    7

    Re: IE Download File Prompt

    vb Code:
    1. Public Sub File_Download_Click_Save(Optional cmb As String)
    2.    
    3.     Dim hWnd As Long
    4.     Dim timeout As Date
    5.    
    6.     'Debug.Print "File_Download_Click_Save"
    7.    
    8.     'Find the File Download window, waiting a maximum of 30 seconds for it to appear
    9.    
    10.     timeout = Now + TimeValue("00:00:30")
    11.     Do
    12.         hWnd = FindWindow("#32770", "File Download")
    13.         DoEvents
    14.         Sleep 200
    15.     Loop Until hWnd Or Now > timeout
    16.    
    17.     'Debug.Print "   File Download window "; Hex(hWnd)
    18.    
    19.     If hWnd Then
    20.         'Find the child Save button
    21.        
    22.         hWnd = FindWindowEx(hWnd, 0, "Button", "&Save")
    23.         'Debug.Print "   Save button "; Hex(hWnd)
    24.     End If
    25.    
    26.     If hWnd Then
    27.    
    28.         'Click the Save button
    29.        
    30.         SetForegroundWindow (hWnd)
    31.         Sleep 600  'this sleep is required and 600 miiliseconds seems to be the minimum that works
    32.         SendMessage hWnd, BM_CLICK, 0, 0
    33.     End If
    34. End Sub
    Works for me,

    M

  8. #8
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Re: IE Download File Prompt

    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
    Last edited by tony007; Feb 17th, 2012 at 12:55 PM.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: IE Download File Prompt

    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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Re: IE Download File Prompt

    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 ?
    Last edited by tony007; Feb 17th, 2012 at 07:11 PM.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: IE Download File Prompt

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: IE Download File Prompt

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width