Solved

Refresh Data Query error

Posted on 2011-02-18
4
303 Views
Last Modified: 2012-05-11
Hi,

I am trying to refresh an OLE DB query with some code.

I get the attached error whenever i run it my routine. The line of code it breaks on is

   Selection.QueryTable.Refresh BackgroundQuery:=False

I can phsyically refresh it on the spreadsheet, just not in the code.

Does anyone know why this is?

Thanks
Seamus
Doc1.doc
0
Comment
Question by:Seamus2626
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:SmittyPro
ID: 34928035
It's probably due to the Selection.  Try referencing the sheet object directly.

HTH,
0
 

Author Comment

by:Seamus2626
ID: 34928092
Heres my complete code, the sheet is called Raw Data, can you suggest?

Thanks
Seamus

    Sheets("Raw Data").Visible = True
    Sheets("Raw Data").Select
    Range("a22").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Range("a24").Select
    Sheets("Screen").Select
       
0
 
LVL 4

Accepted Solution

by:
SmittyPro earned 500 total points
ID: 34928311
For Excel 2007+:

Sheets("Raw Data").Range("A22").ListObject.QueryTable.Refresh BackgroundQuery:=False

For 2003 -:

Sheets("Raw Data").Range("A22").QueryTable.Refresh BackgroundQuery:=False

And if all else fails:

ActiveWorkbook.RefreshAll

The sheet doesn't need to be visible/unhidden.


0
 

Author Closing Comment

by:Seamus2626
ID: 34928346
Good man!

ActiveWorkbook.RefreshAll

That worked

Cheers,
Seamus
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question