VBA：从 URL 打开文本文件进行阅读

VBA: Opening a text file from URL to read

提问by tomocafe

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).

我的网站上有一个文本文件，其中仅包含字符串“1.15”（对于我正在编写的应用程序版本）。在初始化用户表单时，我想从它的 URL 读取该文件并返回字符串“1.15”，以便我可以根据应用程序的版本（存储为常量字符串）检查它。

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.OpenTextmethod, but I don't want to write the string into a workbook. I have tried using the ADODB.LoadFromFileand WinHttp.WinHttpRequest.Openmethods, but both are unable to read the file.

我知道该Workbooks.OpenText方法，但我不想将字符串写入工作簿。我尝试使用ADODB.LoadFromFile和WinHttp.WinHttpRequest.Open方法，但都无法读取文件。

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

任何关于填写内容的建议GetCurrentVersionNumber()将不胜感激。:)

回答by Steven Doggart

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.

虽然它不能直接回答您的问题，但更简单的方法是将其设为 XML 文件而不是文本文件。有更多内置工具可以轻松地从 URL 打开 XML 文件。第二个优点是它还使其更加灵活，因此您以后可以更轻松地将新数据元素添加到 XML 文件中。

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

例如，如果您制作了一个如下所示的http://mywebsite.com/currentversion.xml文件：

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

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

然后，在 VB.NET 中，您可以像这样轻松地阅读它：

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:

或者，在 VBA 中，您可以这样阅读：

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.

您将需要添加对Microsoft XML v?.?的引用。图书馆，不过。

回答by Siddharth Rout

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