PDA

View Full Version : determine if "File Download" window is there



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&parameter_1=" & SWR_start_date & "&parameter_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

DaveK
10-01-2008, 02:31 PM
' now wait for FILE DOWNLOAD dialog box to appear to save the file


OK, with lots of google searching and some lucky guesses, I can determine WHEN the "File Download" popup window appears via VBA.

The next step is to somehow avoid using SENDKEYS to save the file... as this seems to be very unreliable and not repeatable... yikes!

CAN YOU HELP on this: Is there a way to display a Message Box which allows the user to manually save the file from the just-appeared popup window... and THEN allow the VBA to continue when the user hits a message box button saying something to the affect that 'OK, I've manually saved the file... get the next one" ? which would allow the VBA to continue?

OR... better yet, a way to programmatically save the filename as desired and then continue... since the SENDKEYS are not working most of the time!

Dave


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long



Then in the main code module,

' now wait for FILE DOWNLOAD dialog box to appear to save the file

Do Until thewindow <> 0 'wait for the "File Download" popup window to appear
thewindow = FindWindow(vbNullString, "File Download")
Loop

' the file has downloaded... Need to somehow save it
'
' move cursor from 'Cancel' to 'Save' button, then ENTER to bring up 'SAVE AS' popup window
SendKeys "{LEFT}"
Application.Wait Now + TimeValue("00:00:01")
SendKeys "{ENTER}"
Application.Wait Now + TimeValue("00:00:01")
' now we need to save the file
SendKeys ReportYearMonth_ICT_ytd_RAWDATA_fullpath
Application.Wait Now + TimeValue("00:00:01")
SendKeys "{RIGHT}"
Application.Wait Now + TimeValue("00:00:01")
SendKeys "{ENTER}"
Application.Wait Now + TimeValue("00:00:01")

DaveK
10-01-2008, 03:08 PM
This is not as desired, but since I can't get the SENDKEYS to work well, I will go with a modeless USERFORM I'll create which will prompt the user, "Please save the file, and then hit OK".

This brings manual activity from the user into the process, but until I can learn HOW TO AUTOMATICALLY SAVE THE Downloaded file from within VBA, I don't see any other way at the moment.

Any help is greatly appreciated... and I hope my replies will help some others who are/will be asking this same question.

Dave

jasontf
12-16-2008, 01:30 PM
I have the same issue, but I'd like to do this when the computer is locked. Unfortunately, AppActivate and SendKeys don't work when the computer is locked. Anyway, here is my (temporary) solution:

'code to test for the "File download" box and send an s if it is displayed
If Not WaitForBox(ws, "File Download") Then
MsgBox "Download box not displayed"
Exit Sub
End If
Sleep 500: ws.SendKeys "s", True: Sleep 500

'subroutine called above
Public Function WaitForBox(ws As WshShell, sBoxTitle As String, Optional sAppActivateTitle As String = "") As Boolean
Dim i As Integer
If Len(sAppActivateTitle) = 0 Then
sAppActivateTitle = sBoxTitle
End If
For i = 1 To 1200
If GetActiveWindowText <> sBoxTitle Then
Sleep 200
ws.AppActivate sAppActivateTitle
End If
Next i
If GetActiveWindowText = sBoxTitle Then
WaitForBox = True
End If
End Function