| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refreshing External Data Ranges
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing External Data Ranges
hi,
post the rest of your code. I did not see anything that would cause the code to run 9 times. Regards FSt1 "PJFry" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing External Data Ranges
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 "PJFry" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing External Data Ranges
Got it!
ActiveWorkbook.RefreshAll I figured it was a lot easier than I made it... "PJFry" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing External Data Ranges
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 "PJFry" wrote: 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 "PJFry" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop Formulas when refreshing data from external source | Excel Discussion (Misc queries) | |||
| Refreshing external data | Excel Programming | |||
| Import External Text Data and Refreshing Problem | Excel Worksheet Functions | |||
| Query to external data not refreshing | Excel Discussion (Misc queries) | |||
| Refreshing External Data on File Open | Excel Programming | |||
Linear Mode
