VBA - File download from intranet with authorization check RRS feed

  • Question

  • Hello,

    I am new on the forum but already read multiple valuable answers for my case. Thanks to all i was able to reach that point.

    I attempt to automate with VBA file download  from intranet. Authentication is required.

    I have attempt unsuccessfully 2 methods. I would be grateful to any one who could  help moving forward with step 2. I guess there is no way to find workaround in step 1 other than modifying URLDownloadToFile f° or find a security gap in the intranet. 

    Method 1 : Use URLDownloadToFile f°

    Download complete sucessfully BUT downloaded file contains a javascript (send by the server ?) instead of expected datas.

    It seems that URL is redirected to a server which check if user is authenticated  before starting download.  URLDownloadfile is not aware that a redirection should take place and starts immediately to download file

    Method 2 - Launch IE and automate click on "Open, Save, Cancel" dialog box and "Save As" dialog box.

    The below is automated .

    This was done thanks to Sidd Harthrout. Many thanks for the very clear comprehensive code which i slightly modified. Original code can be found on this forum or on his web site.

    Here are the steps 

       - VBA code successfully opens IE and navigates at desired URL.  "File Download" - Dialog box pop's up

      - Click on button "Save" is successful automated and "Save AS" -Dialog box pop's up

       -The VBA code, can successfully select Save button from the "Save As" window BUT click method has no effect. The "Save AS" windows remains open as if click was not received.

    Here is the code I use

    Find_Window_And_Click_Button cTitleWindowFileDownload, cSaveButton Find_Window_And_Click_Button cTitleWindowSaveAS, cSaveButton Find_Window_And_Click_Button cTitleWindowConfirmSaveAs, cYesButton Find_Window_And_Click_Button cTitleWindowDownloadComplete, cCloseButton

    'Save As
    Public Const cTitleWindowSaveAS = "Save As"
    'Public Const cSaveButton = "Save"

    'Confirm save as
    Public Const cTitleWindowConfirmSaveAs = "Confirm Save As"
    Public Const cYesButton = "Yes"

    'Download complete
    Public Const cTitleWindowDownloadComplete = "Download complete"
    Public Const cCloseButton = "Close"

    Option Explicit
    'Imported from
    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, ButtonCaptionRet As Long
    Dim strBuff As String, ButtonCaption As String
    Dim pos As RECT
    Sub Find_Window_And_Click_Button(pWindowTitle As String, pButtonCaption As String)
        '~~> MAIN WINDOW -Get the handle of the "File Download - Security Warning" Window
        Ret = FindWindow(vbNullString, pWindowTitle)
        If Ret <> 0 Then
            Debug.Print "Window : """ & pWindowTitle & """ found. ID = " & Ret
            '~~> BUTTONS WINDOW - 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
                Find_One_Button pButtonCaption
                Click_On_Button ButtonCaptionRet
                 Debug.Print "Child window (button): """ & pButtonCaption & """ not found"
            End If
            Debug.Print "Window : """ & pWindowTitle & """ not found !"
        End If
    End Sub
    'Loop button 'OPEN', 'SAVE', and 'CANCEL' to find appropriate one
    Function Find_One_Button(pButtonCaption As String) As Boolean
        Do While ChildRet <> 0
            Debug.Print "Child window (button) found. ID = " & ChildRet
            'Get the caption of the child window
            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
            GetWindowText ChildRet, strBuff, Len(strBuff)
            ButtonCaption = strBuff
            Debug.Print "Button caption = " & ButtonCaption
            'Check if the caption has the word pButtonCaption ex:"Save"
            If InStr(1, ButtonCaption, pButtonCaption, vbTextCompare) Then
                ButtonCaptionRet = ChildRet
                Exit Do
            End If
            'Get the handle of the next child window
            ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
            'ChildRet = FindWindowEx(Ret, ChildRet, "Edit", vbNullString)
    End Function
    'Click on button based on button ID
    Function Click_On_Button(pButtonID As Long) As Boolean
        'Check if button was found
        If pButtonID <> 0 Then
            Debug.Print "The Handle of save button is : " & pButtonID
            '~~> 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 pButtonID, 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
            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
            Debug.Print "Save button sucessfully clicked !"
            Click_On_Button = True
            Debug.Print "The Handle of Save Button was not found"
            Click_On_Button = False
        End If
    End Function



    • Edited by Etienne06 Friday, January 17, 2014 2:01 PM
    • Moved by Carl Cai Monday, January 20, 2014 8:53 AM (form related
    Friday, January 17, 2014 1:54 PM

All replies

  • You call this "VBA" but it looks like .Net code - what program are you coding with?  Visual Studio, or an Office application, or something else?  If VS, is this a Windows Forms application?

    Also, please explain the authentication; you can pass network credentials along with the connection if the website supports it.  If it only supports forms authentication then you have to be able to navigate the site and perform a login before you can download the file.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, January 17, 2014 4:42 PM
  • Thanks for the answer Reed.

    I am using Excel 2010 and write code as macros.

    Our intranet works with GUARDIAN (not too sure what it is). It requires identification (login) the 1st time you attempt to reach intranet. Then if you close ie and relaunch it you don't need to login again.

    I am not experiencing authentification pb. The issue seems to be that, the URL reachs the GUARDIAN server, who after a quick check redirect to the file SERVER.



    Saturday, January 18, 2014 3:24 PM
  • Hi,

    I have moved this thread to Visual Basic for Applications (VBA) forum for more dedicated supports.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 20, 2014 8:55 AM
  • Hi,

    I might sound simple but i can't find the thread in the VBA forum.

    Would you nicely prodivde a link towards it.



    Friday, January 24, 2014 1:42 PM