Excel VBA QueryTables。添加.Refresh BackgroundQuery错误

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
 
 
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
 
 
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将失败，如果您的查询输入无效或畸形。这个案子的问题是……下一个循环调用单元格作为url，没有赋值。但是，当查询出现故障时，它将失败。
 
 
3 个解决方案
 
 
 
  
#1
 
  
 
  
 
   
4  
 
   
All the previous lines inside the With statement are setting properties.
 the .Refresh BackgroundQuery := False is 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
 
   
刷新是为了刷新结果。后台查询是用于查询SQL数据的，是可选的，所以我认为您可以将它关闭，只使用.Refresh
 
   
Query Table Refresh Method Help Link
 
   
查询表刷新方法帮助链接
 
   
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.
 
   
编辑它会显示URL有问题，当它去刷新它是无法做它。可能是代理问题，或者没有连接到网络，或者URL不存在。
 
  
 
 
 
 
 
  
#2
 
  
 
  
 
   
1  
 
   
The only way to resolve this issue is to delete the active query table after each iteration. A useful example solution provides:
 
   
解决这个问题的唯一方法是在每次迭代之后删除活动查询表。一个有用的示例解决方案提供:
 
   
https://social.technet.microsoft.com/Forums/office/en-US/956dc1b6-bd37-4b97-a042-ba2a37f729b6/removing-querytables-and-leaving-the-results?forum=excel
 
   
https://social.technet.microsoft.com/Forums/office/en-US/956dc1b6-bd37-4b97-a042-ba2a37f729b6/removing-querytables-and-leaving-the-results?forum=excel
 
  
 
 
 
 
 
  
#3
 
  
 
  
 
   
0  
 
   
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.
 
   
我也用querytables。在for循环中添加，我添加了。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
 
  
 
 
 

"))
    .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
Sub Macro1()
Dim URL As String
Dim Path As Stri