Excel網頁外部資料匯入失敗之解決(.Refresh BackgroundQuery:=False)

rockwell

剛好最近需要使用VBA來抓取一些網頁上的資料，所以使用到網頁外部資料匯入，

當開始錄製巨集，就發現出現了錯誤，重複幾次亦是如此，檢視了一下程式碼，

總是在 .Refresh BackgroundQuery:=False 這行程式碼出現錯誤，

換了台電腦試試，同樣的程式碼卻不會發生錯誤‧‧‧所以上網估狗一番。



好了，這篇文章的目的是要記錄可能的解決方式，不多說，直接說答案。

以下是估狗到的解決方式，可以試著試看看。
https://social.msdn.microsoft.com/Forums/zh-TW/f8be93d0-511b-4d8c-9ea0-3b090413f8c0/excel-2010-querytable-code-not-able-open-to-url-getting-error-unable-to-open-url-cannot?forum=exceldev

怕該文章被移除，將內容也貼上好了。

To resolve this situation, there are a couple of things you can do:

1. Edit the web page from where you are pulling your data and remove the no-cache header. You would be looking for a meta tag like the following:<HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">

or

2. You can direct the client computer to set the Internet Explorer option to bypass the no-cache check for SSL sites (remember the https:// in the sample URL above). To do this, go to START and in the RUN line type REGEDIT. In the registry navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings. Right click Internet Settings and left click New > DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes. Double click this value and give it a value of 1.

Then try the web query again.