Getting Run-Time Error '1004' When Running This Code

ryanb909

New Member
Joined
Mar 14, 2013
Messages
12
So I'm getting a Run-Time Error 1004 when I run the code below. Basically it looks for a URL on a separate sheet (Sheet 1) in cell (BA2) and is supposed to scrape the data from the URL in cell BA2 and dump it into Sheet 1's cell of C1. When I run this code I am on Sheet 4. The code drops the 1004 error and when I debug it highlights the ".Refresh BackgroundQuery:=False" line of the code. Not sure why this isn't working, basically looking for another set (or 1000) of eyes. It works fine when I am the Active sheet is Sheet 1 but not when the Active Sheet is Sheet 4. It is driving me nuts.

Code:
Sub PullInfo()


With Sheets("Sheet1").QueryTables.Add(Connection:="URL;" & Range("$BA$2"), Destination:=Sheets("Sheet1").Range("$C$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
According to the QuertyTable help, the .Refresh BackgroundQuery:=False is ;"Used only with query tables based on the results of an SQL query."

Try just this to set the BackgroundQuery argument to false.
.BackgroundQuery = False
 

ryanb909

New Member
Joined
Mar 14, 2013
Messages
12
According to the QuertyTable help, the .Refresh BackgroundQuery:=False is ;"Used only with query tables based on the results of an SQL query."

Try just this to set the BackgroundQuery argument to false.
.BackgroundQuery = False

No luck, When I try to run the macro it never fires off. I could hit it over and over and nothing occurs. Here is the code now:

Code:
Sub PullInfo()


With Sheets("Sheet1").QueryTables.Add(Connection:="URL;" & Range("$BA$2"), Destination:=Sheets("Sheet1").Range("$C$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.BackgroundQuery = False
End With


End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
Now that's a different problem.

Does cell BA2 have a url?

Maybe try this...
Code:
[color=darkblue]Sub[/color] PullInfo()
    
[color=darkblue]With[/color] Sheets("Sheet1").QueryTables.Add(Connection:="URL;" & _
        [COLOR="#FF0000"]Sheets("Sheet1").[/COLOR]Range("BA2"), _
        Destination:=Sheets("Sheet1").Range("C1"))
    .FieldNames = [color=darkblue]True[/color]
    .RowNumbers = [color=darkblue]False[/color]
    .FillAdjacentFormulas = [color=darkblue]False[/color]
    .PreserveFormatting = [color=darkblue]True[/color]
    .RefreshOnFileOpen = [color=darkblue]False[/color]
    .RefreshStyle = xlOverwriteCells
    .SavePassword = [color=darkblue]False[/color]
    .SaveData = [color=darkblue]True[/color]
    .AdjustColumnWidth = [color=darkblue]True[/color]
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "2"
    .WebPreFormattedTextToColumns = [color=darkblue]True[/color]
    .WebConsecutiveDelimitersAsOne = [color=darkblue]True[/color]
    .WebSingleBlockTextImport = [color=darkblue]False[/color]
    .WebDisableDateRecognition = [color=darkblue]False[/color]
    .WebDisableRedirections = [color=darkblue]False[/color]
    .BackgroundQuery = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

ryanb909

New Member
Joined
Mar 14, 2013
Messages
12
Now that's a different problem.

Does cell BA2 have a url?

Maybe try this...
Code:
[COLOR=darkblue]Sub[/COLOR] PullInfo()
    
[COLOR=darkblue]With[/COLOR] Sheets("Sheet1").QueryTables.Add(Connection:="URL;" & _
        [COLOR=#FF0000]Sheets("Sheet1").[/COLOR]Range("BA2"), _
        Destination:=Sheets("Sheet1").Range("C1"))
    .FieldNames = [COLOR=darkblue]True[/COLOR]
    .RowNumbers = [COLOR=darkblue]False[/COLOR]
    .FillAdjacentFormulas = [COLOR=darkblue]False[/COLOR]
    .PreserveFormatting = [COLOR=darkblue]True[/COLOR]
    .RefreshOnFileOpen = [COLOR=darkblue]False[/COLOR]
    .RefreshStyle = xlOverwriteCells
    .SavePassword = [COLOR=darkblue]False[/COLOR]
    .SaveData = [COLOR=darkblue]True[/COLOR]
    .AdjustColumnWidth = [COLOR=darkblue]True[/COLOR]
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "2"
    .WebPreFormattedTextToColumns = [COLOR=darkblue]True[/COLOR]
    .WebConsecutiveDelimitersAsOne = [COLOR=darkblue]True[/COLOR]
    .WebSingleBlockTextImport = [COLOR=darkblue]False[/COLOR]
    .WebDisableDateRecognition = [COLOR=darkblue]False[/COLOR]
    .WebDisableRedirections = [COLOR=darkblue]False[/COLOR]
    .BackgroundQuery = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I think it works now with the original change flipped back...seems to be working at least, LOL! *fingers crossed*...A ton of thanks man!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top