Webquery in a new Excel workbook application

Question

I think the problem here is with reference to new workbook which is opened in the new Excel Application. This is the part of the code were is the error. If to change nb.ActiveSheet.QueryTables.Add to ActiveSheet.QueryTables.Add everything works (but in current workbook, not in nb). It must execute in the new workbook not in current one.

Dim app As New Excel.Application, nb As Excel.Workbook
    Set nb = app.Workbooks.Add
    With nb.ActiveSheet.QueryTables.Add(Connection:= _
         "URL; http://www.example.com", _
             Destination:=Range(Cells(2, 2), Cells(57, 3)))
    End With

You can try to reference the first sheet rather than the ActiveSheet like `nb.Worksheets(1).QueryTables...` — Joseph, Feb 02 '15 at 18:14
Thank you @joseph4tw for your comment, but neither Worksheets nor Worksheets.Range works — Samuel, Feb 02 '15 at 18:20

score 1 · Accepted Answer · answered Feb 02 '15 at 18:29

I see where the problem is, it's here:

Destination:=Range(Cells(2, 2), Cells(57, 3)))

Range() is always going to refer to the ActiveSheet of the ActiveWorkbook if you don't explicitly specify which workbook you want to use. So change this to:

Destination:=nb.Worksheets(1).Range(nb.Worksheets(1).Cells(2, 2), nb.Worksheets(1).Cells(57, 3)))

And that should do it.

EDIT: I should mention the same applies for Cells(), so I updated those as well.

Thank you very much @joseph4tw ! The problem is solved! – Samuel Feb 02 '15 at 18:37 — Samuel, Feb 02 '15 at 18:37

Webquery in a new Excel workbook application

1 Answers1