I while back I needed to be able to read a file off of a web server. I found a couple ways to do so:

• FTP into the webserver (assuming it is your web server and you have a valid login) and download the file locally, then read it like you would any other file
• Use some browser automation (IE) to access the file and read it
• Use the MSXML library to read the file

Using the MSXML Library to Read a File on a WebServer

The latter ended up working the best to suit my needs.  So I thought I’d show you a very simple function which allows you to do so in the hope it could serve someone else trying to do the same thing. Below is some sample code of how it can be done:

'---------------------------------------------------------------------------------------
' Procedure : ReadURLFile
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Access and read a file on a webserver
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFullURLWFile :   Full URL and Filename with the extension
'
' Usage:
' ~~~~~~
' ReadURLFile("http://www.google.ca/index.html")
' ReadURLFile("http://www.SomeDomain.com/SomeFolder/SomeFIle.txt")
' ReadURLFile("http://www.SomeDomain.com/SomeFolder/SomeFIle.dat")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-07-07              Initial Release
' 2         2022-01-13              Updated the Copyright
'                                   Updated (minor) Error Handling
'---------------------------------------------------------------------------------------
Function ReadURLFile(sFullURLWFile As String) As String
'Returns an "" (VbNullString) if an error is reported back from the server
'        otherwise returns the Response Text from the server
'References for further development
'        http://msdn.microsoft.com/en-us/library/ms766431%28v=vs.85%29.aspx
'        http://msdn.microsoft.com/en-us/library/ms763809%28v=vs.85%29.aspx
'        http://en.wikipedia.org/wiki/List_of_HTTP_status_codes

    On Error GoTo Error_Handler
    Dim oHttp           As Object

    Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")

    Call oHttp.Open("GET", sFullURLWFile, False)
    Call oHttp.Send
    'Check for any errors reported by the server
    If oHttp.Status >= 400 And oHttp.Status <= 599 Then
        ReadURLFile = ""
        GoTo Error_Handler
    Else
        ReadURLFile = oHttp.ResponseText
    End If

Error_Handler_Exit:
    On Error Resume Next
    Call oHttp.Close
    Set oHttp = Nothing
    Exit Function

Error_Handler:
    If oHttp.Status >= 400 And oHttp.Status <= 599 Then
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: " & oHttp.Status & vbCrLf & _
               "Error Source: ReadURLFile" & vbCrLf & _
               "Error Description: " & oHttp.StatusText, _
               vbCritical, "An Error has Occurred!"
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ReadURLFile" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

Now, this function can easily be modified to open password protected pages by simply modifying the Open() method, since it has such optional parameters.

A Few Resources on the Subject

List of HTTP status codes - Wikipedia

This is a list of Hypertext Transfer Protocol (HTTP) response status codes. Status codes are issued by a server in response to a client’s request made to the server. It includes codes from IETF Request for Comments (RFCs), other specifications, and some additional codes used in some common applicati…