VBA Code to Download Password Protected Excel File - Server Blocking Request

jlockman

New Member
Joined
Nov 17, 2018
Messages
5
I am trying to use the below code to download a list of daily reports from password protected sites. The server seems to be blocking the request. I have no issues when manually logging in to download each report. I am using Excel 2013 and not positive that this is the correct WinHTTP reference to use or how to modify for use with newer versions, as the thread with the initial code is from 2008 (Haluk). Any advice to modify so that the server sees the request as it would when using a browser manually? I am spending extensive amounts of time to download 14 separate reports every day and really need to get this automated. Any help would be greatly appreciated.


Code:
Sub Download_All_Files()

    Dim i As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    
    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0
    
    If Dir("C:\Users\Jennifer\Dropbox\Daily Payroll Reports", vbDirectory) = Empty Then MkDir "C:\Users\Jennifer\Dropbox\Daily Payroll Reports"
    
    For i = 1 To 10
        MyFile = Cells(i, 1).Text
        If CheckURL(MyFile) Then
            FileNum = FreeFile
            Open "C:\Users\Jennifer\Dropbox\Daily Payroll Reports\LogFile.txt" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , MyFile & " --- Downloaded ----"
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
            TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
            WHTTP.Open "GET", MyFile, False
            WHTTP.Send
            FileData = WHTTP.ResponseBody
            FileNum = FreeFile
            Open "C:\Users\Jennifer\Dropbox\Daily Payroll Reports" & TempFile For Binary Access Write As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
                Put [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , 1, FileData
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
        Else
            FileNum = FreeFile
            Open "C:\Users\Jennifer\Dropbox\Daily Payroll Reports2\Confirmation.txt" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , MyFile & " !!! File Not Found !!!"
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
        End If
    Next
    Set WHTTP = Nothing
    MsgBox "Open the folder [ C:\Users\Jennifer\Dropbox\Daily Payroll Reports ] for the downloaded files..."
End Sub

Function CheckURL(URL) As Boolean
    '
    '
    '
    Dim W As Object
    On Error Resume Next
        Set W = CreateObject("winhttp.winhttprequest.5")
        If Err.Number <> 0 Then
            Set W = CreateObject("winhttp.winhttprequest.5.1")
        End If
    On Error GoTo 0
    
    On Error Resume Next
    W.Open "HEAD", URL, False
    W.Send
    If W.Status = 200 Then
        CheckURL = True
    Else
        CheckURL = False
    End If
End Function
 
Last edited by a moderator:

jlockman

New Member
Joined
Nov 17, 2018
Messages
5
Forgot to mention that I did initially insert code to pass user name and password but still was not successful; the above code is the original code that was given and obviously was not a password protected site.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,996
This is a very difficult task, especially using WinHttpRequest, because you have to emulate a browser's requests and responses exactly. Have you tried IE automation with UIAutomationClient instead? The username and password handling would be a lot easier.

To see what is involved in the WinHttpRequest/XMLhttp method, the code at https://www.mrexcel.com/forum/excel-questions/940433-download-file-intranet-requires-logged-post4550501.html#post4550501 is the final solution in a long thread of trial and error attempts (I think it originally started by trying to combine IE and XMLhttp). The final code would probably be totally different for your site, but it gives an idea of what you may need.
 

jlockman

New Member
Joined
Nov 17, 2018
Messages
5
Thank you, I will check this out. No, I have not tried IE automation with UIAutomationClient, definitely open to that suggestion if you feel it would be easier and can provide a simple code. I'd even settle for having it download one file at a time and skip any notifications or messages or logs. Thanks for your help!
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,996
For example code which uses UIAutomationClient see https://www.mrexcel.com/forum/excel-questions/874093-excel-vba-website-accessing-post4248330.html#post4248330, starting at the line Set o = New CUIAutomation. You must set a reference to UIAutomationClient via Tools > References in the VBA editor.

The code clicks the Save button in the IE11 Notification Bar, so the file will be saved with the default file name in the default download folder. I am currently working on UIAutomationClient code to click the Save As option.
 

jlockman

New Member
Joined
Nov 17, 2018
Messages
5
Thanks so much! I will work with these suggestions and samples and see if I can get this process ironed out. I appreciate the help!
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top