VBA Excel QueryTables.add. BackgroundQuery Update Error

Question

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 above code creates an error on the specified line. A google search in .Refresh BackgroundQuery shows that it is legible in its functions in loops. Just deleting a line does not display anything in excel.

With the current error message, the code works fine for the first value I and then it breaks.

For the answer and comments - TL; DR: .Refresh BackgroundQuery: = False will not work if your request is invalid or incorrect. The problem in this case was this: ... the next loop called cells used as URLs that did not pass any values ​​to them. However, it will fail at any time when the request is malformed.

Answer 1

All previous lines inside the With statement set properties.
.Refresh BackgroundQuery := False - method call.

The update should update the results.
The background query is for querying SQL data and is optional, so I think you can leave it and just have .Refresh

Help link of query table help method

Edit It seems that something is wrong with the URL, and when it goes to update, it cannot do it. may be a proxy problem or may not be connected to the network, or the URL does not exist.

Answer 2

The only way to solve this problem is to delete the active query table after each iteration. A useful sample solution provides:

https://social.technet.microsoft.com/Forums/office/en-US/956dc1b6-bd37-4b97-a042-ba2a37f729b6/removing-querytables-and-leaving-the-results?forum=excel

Answer 3

I'm not sure why my fix works, but here it is:

I also used querytables.add in a for loop, and I added .asc files. This error appeared only after the last addition - therefore, my program essentially did what I wanted, but it would interrupt the function. In the last run of the For loop, I deleted the .Refresh BackgroundQuery: = False statement. He needed to insert my data for all previous runs through the For loop.

I basically replaced this:

  .Refresh BackgroundQuery:=False 

Wherein:

  If Index = ctr Then Else .Refresh BackgroundQuery:=False End If