EXCEL VBA - To open a text file from a website

Question

I want to read from a read the text file from a website such as www.site.com/example.txt and update an tabs in an excel sheet accordingly. Is it possible using excel VBA? Can you advise some examples?

Answer 1

Yes, you can use e.g the http client library to fetch pages from the web.

There are also tools available or such kind of tasks http://www.iopus.com/iMacros/excel.htm?ref=rg8excel1HB&gclid=CLWbrNef2p0CFUtp4wodBnx8sQ.

If you want to do it yourself you have to reference the "Microsoft Internet Transfer" Active X control.

E.g you might fetch the page with buf = HTTPControl.OpenURL(page_you_like_to_fetch, icByteArray) you then can just open it with the usual "suspects" like Open or you can register the file as a ODBC source and fetch it with SQL like statements.

Of course you can also do it by hand. Opening the file, accesing one entry after the other and putting it into the proper Execl cells.

Answer 2

i will like to do it myself. Can you advise what i need to read through so that i will able to acheive. Is there some onlien tutorial or material available. Please help

The below site has an example of how to so it in JAVA. Does anyone has an example for VBA?

http://www.daniweb.com/forums/thread143010.html#

Answer 3

I like Friedrich's suggestion, but if you wanted to do this in an alternative way you could use the shell command from within VBA to launch this process:

Windows Implementation of the Linux WGet command

This is a port of the Linux command which gets the content of a website (URL) to a file from the command line, then it would be possible to open the file which has been created locally in your VBA code and process it accordingly.

(There may be other implementations of this command available, I just linked to the first one that I found on Google)

Answer 4

Take a look at the Workbooks.OpenText method. The filename argument will accept an http URL. The method allows you to open and parse it in one step (assuming its a delimited file).

Application.Workbooks.OpenText("http://somewebsite.com/test.txt", _
  StartRow:=3, _
  DataType:=Excel.XlTextParsingType.xlDelimited, _
  TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, _
  Comma:=True)

Answer 5

Here is an example of how to download a text file.

Requires a reference to Microsoft WinHTTPServices

Sub Test_DownloadTextFile()
    Dim text As String
    text = DownloadTextFile("http://stackoverflow.com/")

    'At this point you can process you file how you wish.
    Debug.Print text
End Sub

'Tool.References... Add a reference to Microsoft WinHTTPServices
Public Function DownloadTextFile(url As String) As String
    Dim oHTTP As WinHttp.WinHttpRequest
    Set oHTTP = New WinHttp.WinHttpRequest
    oHTTP.Open Method:="GET", url:=url, async:=False
    oHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    oHTTP.setRequestHeader "Content-Type", "multipart/form-data; "
    oHTTP.Option(WinHttpRequestOption_EnableRedirects) = True
    oHTTP.send

    Dim success As Boolean
    success = oHTTP.waitForResponse()
    If Not success Then
        Debug.Print "DOWNLOAD FAILED!"
        Exit Function
    End If

    Dim responseText As String
    responseText = oHTTP.responseText

    Set oHTTP = Nothing

    DownloadTextFile = responseText
End Function