CODEWITHSUNDEEP
vbaexcel
tomocafe
tomocafe

Reputation: 1544

VBA: Opening a text file from URL to read

I have a text file on my website that contains only the string "1.15" (for the version of the application I am writing). Upon initialization of the user form, I would like to read that file from its URL and have the string "1.15" returned so that I can check it against the application's version (stored as a const string).

Here is the format I'd like to have...

Const version As String = "1.14"
Const currentVersionURL As String = "http://mywebsite.com/currentversion.txt"

Sub UserForm_Initialize()

    If version <> GetCurrentVersionNumber() Then
        MsgBox "Please update the application."
    End If

End Sub

Function GetCurrentVersionNumber() As String

    ' What do I put here? :(

End Function

I am aware of the Workbooks.OpenText method, but I don't want to write the string into a workbook. I have tried using the ADODB.LoadFromFile and WinHttp.WinHttpRequest.Open methods, but both are unable to read the file.

Any suggestions for what to fill GetCurrentVersionNumber() with would be greatly appreciated. :)

Upvotes: 3

Views: 7852

Answers (2)

Steven Doggart
Steven Doggart

Reputation: 43743

While it doesn't directly answer your question, a simpler approach would be to make it an XML file instead of a text file. There are more built-in tools to easily open an XML file from a URL. The secondary advantage is that it also makes it more flexible, so you can more easily add new data elements to the XML file later on.

For instance, if you made a http://mywebsite.com/currentversion.xml file that looked like this:

<?xml version="1.0" encoding="utf-8" ?>
<AppData>
    <Version>1.14</Version>
</AppData>

Then, in VB.NET you could easily read it like this:

Function GetCurrentVersionNumber() As String
    Dim doc As New XmlDocument()
    doc.Load("http://mywebsite.com/currentversion.xml")
    Return doc.SelectSingleNode("/AppData/Version").InnerText
End Function

Or, in VBA, you could read it like this:

Function GetCurrentVersionNumber() As String
    Dim doc As MSXML2.DOMDocument??  ' Where ?? is the version number, such as 30 or 60
    Set doc = New MSXML2.DOMDocument??
    doc.async = False
    doc.Load("http://mywebsite.com/currentversion.xml")
    GetCurrentVersionNumber = doc.SelectSingleNode("/AppData/Version").Text
End Function

You will need to add a reference to the Microsoft XML, v?.? library, though.

Upvotes: 9

Siddharth Rout
Siddharth Rout

Reputation: 149297

Try this (UNTESTED)

Option Explicit

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

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Const currentVersionURL As String = "http://mywebsite.com/currentversion.txt"
Const version As String = "1.14"

Dim Ret As Long

Sub UserForm_Initialize()
    If version <> GetCurrentVersionNumber() Then
        MsgBox "Please update the application."
    End If
End Sub

Function GetCurrentVersionNumber() As String
    Dim strPath As String

    '~~> Destination for the file
    strPath = TempPath & "currentversion.txt"

    '~~> Download the file
    Ret = URLDownloadToFile(0, currentVersionURL, strPath, 0, 0)

    '~~> If downloaded
    If Ret = 0 Then
        Dim MyData As String, strData() As String

        Open "C:\MyFile.Txt" For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1

        GetCurrentVersionNumber = MyData
    Else
        MsgBox "Unable to download the file"
        GetCurrentVersionNumber = ""
    End If
End Function

'~~> Get Users Temp Path
Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

Upvotes: 1

Related Questions