VBA Excel QueryTables.add .Refresh BackgroundQuery错误 [英] VBA Excel QueryTables.add .Refresh BackgroundQuery Error

问题描述

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$1"))
    .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

上面的代码在指定的行上创建一个错误。一个谷歌搜索.Refresh BackgroundQuery显示它在循环中的功能是挑剔的。只需删除该行就可以在excel中显示。

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.

使用当前错误消息，代码对第一个i值有效，然后中断。

With the current error message the code works fine for the first i value and then breaks.

对于答案和评论 -
TLDR：.Refresh BackgroundQuery：=如果您的查询输入无效或格式错误，False将失败。在这种情况下的问题是for ...下一个循环正在调用单元格使用url，它们没有值。但是它会在查询错误的时候失败。

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.

推荐答案

With语句中的所有以前的行都是设置属性。
.Refresh BackgroundQuery：= False 是一种方法调用。

All the previous lines inside the With statement are setting properties.
the .Refresh BackgroundQuery := False is a method call.

刷新应该刷新结果。

后台查询是用于查询SQL数据并且是可选的，所以我认为您可以将其关闭，只需具有.Refresh

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

查询表刷新方法帮助链接

修改
看起来有什么问题URL，何时刷新它是无法做到的。可能是代理问题，或未连接到网络，或该URL不存在。

Edit It 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.

这篇关于VBA Excel QueryTables.add .Refresh BackgroundQuery错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！