VBA Excel QueryTables.add .Refresh BackgroundQuery 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 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.

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

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.

what does .Refresh BackgroundQuery even do?

iwishiwasacodemonkey
– iwishiwasacodemonkey

2011-04-05 23:54:48 +00:00
Commented Apr 5, 2011 at 23:54 — iwishiwasacodemonkey
– iwishiwasacodemonkey, Commented Apr 5, 2011 at 23:54

Nathan Fisher · Accepted Answer · 2011-04-06 01:45:50Z

4

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

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

edited Apr 6, 2011 at 1:45

answered Apr 6, 2011 at 1:38

Nathan Fisher

7,9394 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

iwishiwasacodemonkey Over a year ago

keep in mind the code uses urls supplied in a spreadsheet so if you want the code to work you need to input your own i am using "flvtubesearch.co/…" as the url and "?tmp=toolbar_FlvTube_homepage&prt=flvtubetb04ie&clid=acd050b75c114e5ba9a270e770c9cf6f" as the path

iwishiwasacodemonkey Over a year ago

also changing the .refresh backgroundquery: = False to .refresh = false fails and gives the error "object doesn't support this method"

iwishiwasacodemonkey Over a year ago

On another note if you change the for next loop to for i=2 to 2 the code runs just find, so the problem is somehow related to for...next loops

Nathan Fisher Over a year ago

sorry I meant that just use .Refresh

Nathan Fisher Over a year ago

do you realise that you are putting the data into the same range each time. so you will end up with 48 querytables all pointing to the same spot which is probably where the problem is

|

R. Oosterholt · Accepted Answer · 2017-03-27 14:04:50Z

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

edited Mar 27, 2017 at 14:04

R. Oosterholt

8,2003 gold badges56 silver badges80 bronze badges

answered Aug 1, 2011 at 12:21

a fascinated excel user

111 bronze badge

1 Comment

Alex McManns Over a year ago

The resource cannot be found.

Shizinator · Accepted Answer · 2016-06-10 13:57:44Z

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.

Basically I replaced this:

          .Refresh BackgroundQuery:=False

With this:

          If Index = ctr Then

          Else
               .Refresh BackgroundQuery:=False
          End If

answered Jun 10, 2016 at 13:57

Shizinator

1

Collectives™ on Stack Overflow

VBA Excel QueryTables.add .Refresh BackgroundQuery Error

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related