web queries in excel - Run-time error '-2147024809 (80070057)' 1

[Aubs010]

I'm using web queries in excel.


The following is the code I use to gather the info from "[ignore]

http://somewebaddress/MyInfo?Aubs

[/ignore]"

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveWorkbook.ActiveSheet.Name = "Data Collection"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://somewebaddress/MyInfo?Aubs"[/URL] _
        , Destination:=Range("A1"))
        .Name = "MyInfo?Aubs"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
ActiveWorkbook.Sheets("List").Select

If I have a button on the sheet called "List", and call the above code, I get:

Run-time error '-2147024809 (80070057)':

The destination range is not on the same worksheet that the query table is being created on.

I have created a macro, using Excel's built in writer, and this is the code it provides, which works fine!

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/11/2003 by Aubs
'

'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://setiathome2.ssl.berkeley.edu/fcgi-bin/fcgi?email=test@test.com&cmd=user_stats_new"[/URL] _
        , Destination:=Range("A1"))
        .Name = "fcgi?email=test@test.com&cmd=user_stats_new"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Any help would be greatly appreciated.

Aubs

 

[xlbo]

try this:

dim newSht as worksheet
set NewSht = ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
NewSht.Name = "Data Collection"
With NewSht.QueryTables.Add(Connection:= _
"URL;

http://somewebaddress/MyInfo?Aubs"

_
, Destination:=NewSht.Range("A1&quot)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Aubs010]

Thanks for the code Geoff,

Unfortunately... guess... yup, the same error!

It seems as if the sheet calling has to be the same sheet as the table and result...

ooer, wait!

I got it... has to be:

Dim NS As Worksheet
Set NS = ActiveWorkbook.Sheets.Add
NS.Name = "Data Collection"
    With NS.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://http://somewebaddress/MyInfo?Aubs"[/URL] _
        , Destination:=

NS.

Range("A1"))

Thanks for your help Geogg, much appreciated... a

for you me thinks for the inspiration and very near answer!

Regards,

Aubs

 

[xlbo]

um - don't wanna be a killjoy here but isn't that exactly what I posted ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Aubs010]

Um, yeah, sorry, it was!!!!

I didn't notice that little bit!! - Saw the first and thought that was the only mod you made... Will teach me to look more closely!

Thanks again



Aubs