Refreshing External Data Ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PJFry - 24 Sep 2007 21:36 GMT

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

Reply to this Message

FSt1 - 24 Sep 2007 23:14 GMT

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

Regards
FSt1

> I have a macro that I recorded to update an external range named
> Ralston_SupplierInput. The data come an Access db.
[quoted text clipped - 17 lines]
> Thanks!
> PJ

Reply to this Message

PJFry - 24 Sep 2007 23:20 GMT

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

> I have a macro that I recorded to update an external range named
> Ralston_SupplierInput. The data come an Access db.
[quoted text clipped - 17 lines]
> Thanks!
> PJ

Reply to this Message

FSt1 - 24 Sep 2007 23:44 GMT

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

> 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
[quoted text clipped - 54 lines]
> > Thanks!
> > PJ

Reply to this Message

PJFry - 24 Sep 2007 23:26 GMT

Got it!

ActiveWorkbook.RefreshAll

I figured it was a lot easier than I made it...

> I have a macro that I recorded to update an external range named
> Ralston_SupplierInput. The data come an Access db.
[quoted text clipped - 17 lines]
> Thanks!
> PJ

Reply to this Message