Excel VBA how to read in text file from web (not c

问题:

I have been using the following code to read in a text file from the web:

'import the text file into a string
Function DownloadTextFile(URL As String) As String
On Error GoTo Err_GetFromWebpage

Dim objWeb As Object
Dim strXML As String

 ' Instantiate an instance of the web object
Set objWeb = CreateObject("Microsoft.XMLHTTP")

 ' Pass the URL to the web object, and send the request
objWeb.Open "GET", URL, False
objWeb.send

 ' Look at the HTML string returned
strXML = objWeb.responseText

DownloadTextFile = strXML


End_GetFromWebpage:
 ' Clean up after ourselves!
Set objWeb = Nothing
Exit Function

Err_GetFromWebpage:
 ' Just in case there's an error!
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_GetFromWebpage

End Function

It worked fine the first time, but when I make changes to the text file, it's not being reflected in the returned string. It's as if Excel is caching the file. What can I do to fix this? Thanks!

回答1:

Without having the URL it is not possible to test it, but have you tried to use setRequestHeader with no-cache?

Example:

objWeb.setRequestHeader "Content-Type", "text/xml"
objWeb.setRequestHeader "Cache-Control", "no-cache"
objWeb.setRequestHeader "Pragma", "no-cache"

回答2:

In addition to setting the request headers, as suggested by @dee, I've also changed "Microsoft.XMLHTTP" to "Msxml2.ServerXMLHTTP", and now it works! Here is the final code:

'import the text file into a string
Function DownloadTextFile(URL As String) As String
    On Error GoTo Err_GetFromWebpage

    Dim objWeb As Object
    Dim strXML As String

     ' Instantiate an instance of the web object
    Set objWeb = CreateObject("Msxml2.ServerXMLHTTP")

     ' Pass the URL to the web object
    objWeb.Open "GET", URL, False

    'don't cache the file
    objWeb.setRequestHeader "Content-Type", "text/xml"
    objWeb.setRequestHeader "Cache-Control", "no-cache"
    objWeb.setRequestHeader "Pragma", "no-cache"

    'send the request
    objWeb.send

     ' Look at the HTML string returned
    strXML = objWeb.responseText

    DownloadTextFile = strXML

    End_GetFromWebpage:
     ' Clean up after ourselves!
    Set objWeb = Nothing
    Exit Function

    Err_GetFromWebpage:
     ' Just in case there's an error!
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume End_GetFromWebpage 

End Function