DaveK
10-01-2008, 10:16 AM
Hi again Forum experts,
My Excel VBA successfully opens Internet Explorer and requests an intranet file download... and EVENTUALLY (30-90 seconds later) a "File Download" Windows dialog box appears.
HOW CAN VBA automatically wait for this window to finally appear, and then process it as desired by saving the file with my chosen filename???
The title of the window is "File Download", with the question, "Do you want to open or save this file?", with 3 buttons,
OPEN SAVE CANCEL
When I manually hit the SAVE button, this then brings up another typical dialog box titled "SAVE AS", with a scrollable list of files and the SAVE or CANCEL button.
What of course I want VBA to do next is to automatically save the file with the desired name and path which is known within the VBA.
Is there some way for the VBA to search for all open windows, and continually look for a window to appear with the title = "File Download", and then somehow hit the save button and then do the same with the "Save As" dialog box window... to automatically save the file and then continue the VBA?
This surely is a common question, but I've spent hours searching the Forum and can't find any real answers to this.
Sub ICT_SWR_8541_v2()
'
' set reference to Microsoft Internet Controls
'
' note: user must be logged into intranet before executing this VBA
Dim IE As InternetExplorer
Dim SWR_url As String
SWR_start_date = "01-Aug-2008"
SWR_end_date = "30-Aug-2008"
SWR_url = "http://intranet.work.com/cgi/swr/swr_run.pl?report=8541&action=U&mode=R&app=swr&outputType=2¶meter_1=" & SWR_start_date & "¶meter_2=" & SWR_end_date
'========================
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Left = 10
.Top = 10
.Height = 600
.Width = 600
.MenuBar = 0
.Toolbar = 0
.StatusBar = 0
.Navigate SWR_url
.Visible = True
End With
Do
Loop Until IE.ReadyState = READYSTATE_COMPLETE
Do
Loop Until IE.Document.ReadyState = "complete"
' now wait for FILE DOWNLOAD dialog box to appear to save the file
YOUR HELP IS MUCH APPRECIATED as always
Dave
My Excel VBA successfully opens Internet Explorer and requests an intranet file download... and EVENTUALLY (30-90 seconds later) a "File Download" Windows dialog box appears.
HOW CAN VBA automatically wait for this window to finally appear, and then process it as desired by saving the file with my chosen filename???
The title of the window is "File Download", with the question, "Do you want to open or save this file?", with 3 buttons,
OPEN SAVE CANCEL
When I manually hit the SAVE button, this then brings up another typical dialog box titled "SAVE AS", with a scrollable list of files and the SAVE or CANCEL button.
What of course I want VBA to do next is to automatically save the file with the desired name and path which is known within the VBA.
Is there some way for the VBA to search for all open windows, and continually look for a window to appear with the title = "File Download", and then somehow hit the save button and then do the same with the "Save As" dialog box window... to automatically save the file and then continue the VBA?
This surely is a common question, but I've spent hours searching the Forum and can't find any real answers to this.
Sub ICT_SWR_8541_v2()
'
' set reference to Microsoft Internet Controls
'
' note: user must be logged into intranet before executing this VBA
Dim IE As InternetExplorer
Dim SWR_url As String
SWR_start_date = "01-Aug-2008"
SWR_end_date = "30-Aug-2008"
SWR_url = "http://intranet.work.com/cgi/swr/swr_run.pl?report=8541&action=U&mode=R&app=swr&outputType=2¶meter_1=" & SWR_start_date & "¶meter_2=" & SWR_end_date
'========================
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Left = 10
.Top = 10
.Height = 600
.Width = 600
.MenuBar = 0
.Toolbar = 0
.StatusBar = 0
.Navigate SWR_url
.Visible = True
End With
Do
Loop Until IE.ReadyState = READYSTATE_COMPLETE
Do
Loop Until IE.Document.ReadyState = "complete"
' now wait for FILE DOWNLOAD dialog box to appear to save the file
YOUR HELP IS MUCH APPRECIATED as always
Dave