Excel VBA如何从Web读取文本文件(未缓存)? [英] Excel VBA how to read in text file from web (not cached)?
本文介绍了Excel VBA如何从Web读取文本文件(未缓存)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我一直在使用以下代码从网络中读取文本文件:
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
第一次运行良好,但是当我对文本文件进行更改时,它并没有反映在返回的字符串中。就像Excel正在缓存文件一样。我该怎么做才能解决此问题?谢谢!
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!
推荐答案
除了设置请求标头(如@dee所建议的那样),我还更改了 Microsoft.XMLHTTP 添加到 Msxml2.ServerXMLHTTP,现在可以使用了!这是最终的代码:
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
这篇关于Excel VBA如何从Web读取文本文件(未缓存)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文