-
Apr 6th, 2011, 03:19 PM
#1
Thread Starter
New Member
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.
-
Apr 6th, 2011, 04:19 PM
#2
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
-
Apr 8th, 2011, 08:59 AM
#3
Thread Starter
New Member
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.
-
Apr 8th, 2011, 11:00 PM
#4
Thread Starter
New Member
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
-
Apr 26th, 2011, 08:32 AM
#5
New Member
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 or .
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
-
Feb 17th, 2012, 11:02 AM
#6
Frenzied Member
Re: IE Download File Prompt
could you guys post a working code that clicks save automatically ?
-
Feb 17th, 2012, 11:14 AM
#7
New Member
Re: IE Download File Prompt
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
-
Feb 17th, 2012, 12:40 PM
#8
Frenzied Member
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.
-
Feb 17th, 2012, 03:42 PM
#9
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
-
Feb 17th, 2012, 06:50 PM
#10
Frenzied Member
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.
-
Feb 18th, 2012, 04:36 AM
#11
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
-
Feb 22nd, 2012, 09:10 PM
#12
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|