We help IT Professionals succeed at work.

Excel VBA to download a file from Internet Explorer

pmcd2012
pmcd2012 asked
on
Medium Priority
8,059 Views
Last Modified: 2012-05-12
I need help controlling internet explorer to allow me to download a specific file and save it to a specific location without user input.  I already have the code to control the explorer window and I know how to navigate using VBA.  The only problem I have is getting past the pop-up window that asks me to Open, Save or Cancel.  Once I get past that, I will need to tell the window where I want the file saved and the name I wanted it saved as.  I can't provide the internet website as it is a secure website that I can't give login information to.  I will also need to tell the "Download Complete" window to close once the download is done.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Maybe this will help

http://siddharthrout.wordpress.com/2011/10/23/vbavb-netvb6click-opensavecancel-button-on-ie-download-window/

Initially I thought that using API FindWindow, FindWindowEx and Sendmessage would be enough but soon I realized that like normal windows the download window remains unresponsive to SendMessage API. So finally I had to use a workaround and then I was able to click on the Open/Save/Cancel button.

Like you and me, we both have names, similarly windows have “handles” (hWnd), Class etc. Once you know what that hWnd is, it is easier to interact with that window.

Findwindow API finds the hWnd of a particular window by using the class name and the caption of the window (“File Download”) in this case. To test it, here is a sample code

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

Dim Ret As Long

Sub Sample()
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Window Found"
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Author

Commented:
Thank you for the quick response.  There is apparently a small wrinkle to the issue.  The files aren't actually linked to on the website.  The website uses the following HTML/JAVA to link to the file that is shown on the screen:

<a href="/supplierservices/forms/intervalFileDownload.do?id=PP">PP INTVL 201110 DATA.zip</a>

All I need to do is grab the zip file but the link is a java script call.  Additionally, when I use the code from the website you linked to, it will not find the child window.
CERTIFIED EXPERT

Commented:
Did you try the finalized version of the code?

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

Private 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

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long

Private Declare Sub SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal _
hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As _
Long, ByVal cy As Long, ByVal wFlags As Long)

Private Declare Function SetCursorPos Lib "user32" _
(ByVal X As Integer, ByVal Y As Integer) As Long

Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Declare Sub mouse_event Lib "user32.dll" (ByVal dwFlags As Long, _
ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

'~~> Constants for pressing left button of the mouse
Private Const MOUSEEVENTF_LEFTDOWN As Long = &H2
'~~> Constants for Releasing left button of the mouse
Private Const MOUSEEVENTF_LEFTUP As Long = &H4

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40

Dim Ret As Long, ChildRet As Long, OpenRet As Long
Dim strBuff As String, ButCap As String
Dim pos As RECT

Sub Sample()
    '~~> Get the handle of the "File Download" Window
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"
        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not
        If ChildRet <> 0 Then
            MsgBox "Child Window Found"

            '~~> Get the caption of the child window
            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
            GetWindowText ChildRet, strBuff, Len(strBuff)
            ButCap = strBuff

            '~~> Loop through all child windows
            Do While ChildRet <> 0
                '~~> Check if the caption has the word "Open"
                '~~> For "Save" or "Cancel", replace "Open" with
                '~~> "Save" or "Cancel"
                If InStr(1, ButCap, "Open") Then
                    '~~> If this is the button we are looking for then exit
                    OpenRet = ChildRet
                    Exit Do
                End If

                '~~> Get the handle of the next child window
                ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
            Loop

            '~~> Check if we found it or not
            If OpenRet <> 0 Then
                MsgBox "The Handle of Open Button is : " & OpenRet

                '~~> Retrieve the dimensions of the bounding rectangle of the
                '~~> specified window. The dimensions are given in screen
                '~~> coordinates that are relative to the upper-left corner of the screen.
                GetWindowRect OpenRet, pos

                '~~> Move the cursor to the specified screen coordinates.
                SetCursorPos (pos.Left - 10), (pos.Top - 10)
                '~~> Suspends the execution of the current thread for a specified interval.
                '~~> This give ample amount time for the API to position the cursor
                Sleep 100
                SetCursorPos pos.Left, pos.Top
                Sleep 100
                SetCursorPos (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2

                '~~> Set the size, position, and Z order of "File Download" Window
                SetWindowPos Ret, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
                Sleep 100

                '~~> Simulate mouse motion and click the button
                '~~> Simulate LEFT CLICK
                mouse_event MOUSEEVENTF_LEFTDOWN, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0
                Sleep 700
                '~~> Simulate Release of LEFT CLICK
                mouse_event MOUSEEVENTF_LEFTUP, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0
            Else
                MsgBox "The Handle of Open Button was not found"
            End If
        Else
             MsgBox "Child Window Not Found"
        End If
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Author

Commented:
Yea, it gives me either Child Window Not Found or Window Not Found.
CERTIFIED EXPERT

Commented:
Apparently that is a dynamic page generated with Java.

Maybe this will help

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/beb6fa0e-fbc8-49df-9f2e-30f85d941fad/

Declare Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long

Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal      As Long
    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

Sub downFLash()
    Dim sURL    As String
    Dim LocalFilename   As String
    Dim filename As String
   
    Const UNC = "C:\Documents and Settings\All Users\Documents\My Music\"
   
    filename = "20050628091444_1.mp3"
    sURL = "http://jrc.donga.com/nihao_service/200506/" & filename
    LocalFilename = UNC & filename

    Debug.Print DownloadFile(sURL, LocalFilename)
   
End Sub

Author

Commented:
Ok.  I just got the first set of code to work up to getting the thing to click on the save button.  Now, I need to know how to make it select a folder and change the file name.  Is this possible?
CERTIFIED EXPERT

Commented:
You could try something like this

http://www.fmsinc.com/free/newtips/Access/accesstip31.asp


The Name statement moves the file to the new directory or folder and renames the file, if necessary. Here's the syntax:

  Name OldPathName As NewPathName

Name can move a file across drives, but it can only rename an existing directory or folder when both OldPathName and NewPathName are located on the same drive. Name cannot create a new file, directory, or folder.

If OldPathName and NewPathName have different paths, and the same file name, the Name statement moves the file to the new location and leaves the file name unchanged.

Using Name, you can move a file from one directory or folder to another, but you cannot move a directory or folder.

Using Name on an open file produces an error. You must close an open file before renaming it. Name arguments cannot include multiple-character (*) and single-character (?) wildcards.

Author

Commented:
This isnt going to help me finish the save as dialog box or choose a folder and filename.  this whole routine needs to be automatic.
CERTIFIED EXPERT

Commented:
You could let it just download in the default location and then move it with something like this.

http://www.rondebruin.nl/folder.htm

Sub Move_Rename_One_File()
'You can change the path and file name
    Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"
End Sub

Author

Commented:
Ok.  So how do I do the following?

-Finish the save as dialog box
-Find the right file to rename
CERTIFIED EXPERT
Commented:
Maybe this would work for you

http://stackoverflow.com/questions/365391/using-vb-to-automate-ie-save-target-as

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
...
Sub YourMacro()
    ... Navigate IE to the correct document, and get it to pop
    up the "Save As" dialog ...

    Set sh = CreateObject("WScript.Shell")
    sh.AppActivate "File Download"
    sh.SendKeys "S"
    Sleep 100
    sh.SendKeys "C:\Path\filename.ext{ENTER}"
End Sub
>>>Yea, it gives me either Child Window Not Found or Window Not Found.

@pmcd2012: Could you please share the link that you are navigating to. Also please confirm if you are using IE.

Sid

Author

Commented:
Ok, I was able to get everything done so thank you very much Number-1.  I have a few other issues that I need to resolve that come after all of this but I am going to post a new question as I am using a vbscript in a .vbs file to complete the next steps.