CODEWITHSUNDEEP
excelvba
JTFRage
JTFRage

Reputation: 397

Download log file from web server using VBA

The setup

Server generates logs files located at https://myserver.com/logs/. The /logs directory is password protected with an .htaccess file. Trying to access https://myserver.com/logs/ will prompt the user for username and password in order to see the directory listing.

What I'm trying to do:

I am writing a VBA script in MS Excel to download a log file from my server. The log file server.log sits in the /logs directory. I want to download the file to my local drive at C:\Downloads\server.log. Ultimately I want to write a for loop to download multiple files but I just want to be able to download just one for now.

The problem I'm having:

I am getting <h1>Access Denied</h1> as the return message. I don't see any files downloaded. Weird thing though... yesterday it would download the file but only after I access it and open it up via IE. Then each subsequent download worked fine. However today this doesn't work anymore.

My function to download a file:

Private 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 downloadLog()
        Dim URL As String
        Dim DestFile As String
        Dim Res As Long

        URL = "https://myserver.com/logs/server.log"
        DestFile = "C:\Downloads\server.log"

        Res = URLDownloadToFile(0&, URL, DestFile, 0&, 0&)

        If Res = 0 Then
            MsgBox "Success"
        Else
            MsgBox "Failure"
        End If
    End Function

Please help... I'm all out of ideas.

Upvotes: 1

Views: 1907

Answers (3)

user3108621
user3108621

Reputation:

I ran into similar problem before and here is the solution:

Use the ADODB.Stream object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oStream = CreateObject("ADODB.Stream")

Upvotes: 1

JTFRage
JTFRage

Reputation: 397

I found the answer somewhere else. I'm posting it here so that others who run into the same issue can use the same function below to accomplish the same thing.

You'll be prompted to enter the username and password when it first attempts but then every other attempt is seamless. Works just like I wanted it.

Function FetchFile(sURL As String, sPath)
    Dim oXHTTP As Object
    Dim oStream As Object


    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    Set oStream = CreateObject("ADODB.Stream")
    'Application.StatusBar = "Fetching " & sURL & " as " & sPath
    oXHTTP.Open "GET", sURL, False
    oXHTTP.send

    If (oXHTTP.Status = 200) Then
        With oStream
            .Type = 1 'adTypeBinary
            .Open
            .Write oXHTTP.ResponseBody
            .SaveToFile sPath, 2 'adSaveCreateOverWrite
            .Close
        End With
        FetchFile = True
    Else
        FetchFile = False
    End If

    Set oXHTTP = Nothing
    Set oStream = Nothing
    'Application.StatusBar = False
End Function

Upvotes: 2

Blackhawk
Blackhawk

Reputation: 6120

I don't have a similar site on which I can test code, but here's something that might be worth trying. Microsoft has a solution for authenticating with WinINeton this site under the section titled "URLs that are opened by objects that call WinInet or Urlmon functions". They suggest that using the WinINet library.

First, call InternetOpen to get an HINTERNET handle. Next, call InternetConnect with the INTERNET_DEFAULT_HTTPS_PORT option, providing lpszUsername and lpszPassword.

Use HttpOpenRequest and HttpSendRequest to make your request for the file url.

If all goes according to plan, you can then call InternetReadFile to get the data.

Here are examples of authentication with WinINet, not sure which fits your needs.

Upvotes: 0

Related Questions