VBA error

[ajaykon]

Hi all,
I have the following VBA code in the macro for my Excel SpreadSheet, but the macro is giving an error " RunTime error '1004', Application-defined or object-defined error".
I am on Win2k, MS-Office2K.



Range("B3").Select
Selection.QueryTable.Refresh BackgroundQuery:=false


The error points to the second line i.e. "Selection..."
I would also add that I am a newbee to VB, my strengts lie elsewhere. This was needed to transfer some upload some data to the spreadsheet

TIA

Ajay K

[GungaDin]

If you are doing this in VB, you will find VB very unhappy when dealing with the Selection object. You're best bet whenever dealing with code generated by Excel that ends up with a Selection object is to replace it with the Range you have selected.

ie. Your code of:


Range("B3").Select
Selection.QueryTable.Refresh BackgroundQuery:=false




Would become

Range("B3").QueryTable.Refresh BackgroundQuery:=false




Hope this helps,

Nathan Liebke

[GungaDin]

I just had another thought.

If you are NOT doing this in VB, you will find that the problem is due to the fact that the cell you are selecting (in this case B3) is not actually part of a Query Table. You need to select a cell that will definitely be part of the Query Table you have selected.

The column headers are generally a safe cell to select. Any other cells can potentially not be part of the Query if it returns no data.

Another safer way of refreshing the data is to refer directly to the query table.

The following will refresh all Queries on a sheet (Sheet1 in this example):


Dim i as Integer

for i = 1 to Worksheets("Sheet1").QueryTables.Count
Worksheets("Sheet1").QueryTables(i).Refresh BackgroundQuery:=false
next i




Hope this helps,

Nathan Liebke