Refreshing External Data Ranges


G

Guest

I have a macro that I recorded to update an external range named
Ralston_SupplierInput. The data come an Access db.

Here is a piece of the code:

'Move the the sheet containing the external data range
Sheets("SupplierInput").Select
'Select a cell in the data range
Range("C1").Select
'Refresh the range
Selection.QueryTable.Refresh BackgroundQuery:=False

It runs the same code nine times, selecting in a new sheet and range each
time. Is there a way to refresh this data range by simply referencing the
name of the range?

My preference is to have more control over the process than having an
automatic refresh or needing to open and close the document to refresh.

Thanks!
PJ
 
Ad

Advertisements

G

Guest

hi,
post the rest of your code. I did not see anything that would cause the code
to run 9 times.

Regards
FSt1
 
G

Guest

It is designed to run nine times. I just hit the record button and refreshed
everything manually. If I change the order of the sheets or move the
location of the external data the whole thing would break.

If I can reference the names directly, then I won't have to worry about
order.


Sheets("SupplierInput").Select
Range("C1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A19").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A5").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A5").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
 
Ad

Advertisements

G

Guest

hi again,
well lets's not do the activesheet.next.select thing. that i think is what
is messing your up.
instead select each sheet. like this....

sheets("sheet1").select 'change to your sheet names
Range("a1").select 'change to your range
Selection.QueryTable.Refresh BackgroundQuery:=False

sheets("sheet2").select 'change to your sheet names
Range("a1").select 'change to your range
Selection.QueryTable.Refresh BackgroundQuery:=False

sheets("sheet3").select 'change to your sheet names
Range("a1").select 'change to your range
Selection.QueryTable.Refresh BackgroundQuery:=False

ect. this would do 3 of the MSQ's. add untill you hit them all.
also. you might want to look up the querytalble.refreshall method in vb
help.(not xl help. click on help in the vb editor - alt+F11) But with 9
MSQ's, that might bog things down some.
my thoughts
Regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top