VBA Excel QueryTables.add .Refresh BackgroundQuery 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5559644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel QueryTables.add .Refresh BackgroundQuery Error
提问by iwishiwasacodemonkey
Sub Macro1()
Dim URL As String
Dim Path As String
Dim i As Integer
For i = 2 To 50
If Range("Prices!E" & i).Value <> 1 Then
URL = Range("Prices!D" & i).Text
Path = Range("Prices!F" & i).Text
End If
Sheet19.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & URL _
, Destination:=ActiveSheet.Range("$A"))
.Name = _
"" & Path
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
//'In the Line above the above
//'Run time error '1004
//'An unexpected error has occured
End With
Next i
End Sub
The code above creates an error at the specified line. A google search on .Refresh BackgroundQuery shows that it is picky in its functionality in loops. Simply deleting the line makes nothing show up in excel.
上面的代码在指定的行上创建了一个错误。对 .Refresh BackgroundQuery 的谷歌搜索表明它在循环中的功能很挑剔。简单地删除该行不会在 excel 中显示任何内容。
With the current error message the code works fine for the first i value and then breaks.
使用当前的错误消息，代码对第一个 i 值工作正常，然后中断。
For Answer and comments- TLDR: .Refresh BackgroundQuery:=False will fail if your query input is invalid or malformed. The problem in this case was the for...next loop was calling cells to use as url's that hand no values in them. However it will fail anytime the query is malformed.
对于答案和评论 - TLDR: .Refresh BackgroundQuery:=False 如果您的查询输入无效或格式错误，将会失败。在这种情况下的问题是 for...next 循环调用单元格以用作 url，其中没有任何值。但是，只要查询格式错误，它就会失败。
采纳答案by Nathan Fisher
All the previous lines inside the With statement are setting properties.
the .Refresh BackgroundQuery := Falseis a method call.
With 语句中的所有前几行都是设置属性。
这.Refresh BackgroundQuery := False是一个方法调用。
The refresh is supposed to refresh the results.
The background Query is for when quering SQL data and is optional so I think you can leave it off and just have .Refresh
刷新应该刷新结果。
后台 Query 用于查询 SQL 数据并且是可选的，所以我认为您可以不使用它，只需要 .Refresh
Query Table Refresh Method Help Link
EditIt would appear that there is something wrong with the URL and when it goes to refresh it is unable to do it. could be a proxy issue, or not connected to the network, or the URL does not exist.
编辑看起来 URL 有问题，当它刷新时无法执行。可能是代理问题，或者没有连接到网络，或者 URL 不存在。
回答by a fascinated excel user
The only way to resolve this issue is to delete the active query table after each iteration. A useful example solution provides:
解决此问题的唯一方法是在每次迭代后删除活动查询表。一个有用的示例解决方案提供：
回答by Shizinator
I'm not sure why my fix worked, but here it is:
我不确定为什么我的修复有效，但这里是：
I also used querytables.add within a for loop, and I was adding .asc files. This error was only popping up after the last addition--so my program essentially did what I wanted it to, but it would interrupt function. On the last run through the For loop, I removed the .Refresh BackgroundQuery:=False statement. It was necessary for it to paste my data for all the previous runs through the For loop.
我还在 for 循环中使用了 querytables.add，并添加了 .asc 文件。这个错误只是在最后一次添加之后才出现——所以我的程序基本上做了我想要的，但它会中断功能。在 For 循环的最后一次运行中，我删除了 .Refresh BackgroundQuery:=False 语句。它有必要通过 For 循环粘贴所有以前运行的数据。
Basically I replaced this:
基本上我替换了这个：
.Refresh BackgroundQuery:=False
With this:
有了这个：
If Index = ctr Then
Else
.Refresh BackgroundQuery:=False
End If
