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

[ryanb909]

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.

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

 

[AlphaFrog]

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]

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:

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]

Now that's a different problem.

Does cell BA2 have a url?

Maybe try this...

[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]

Now that's a different problem.

Does cell BA2 have a url?

Maybe try this...

[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!