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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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,450
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,157
Messages
5,835,715
Members
430,382
Latest member
legolas97

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