Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to refresh data
Hi All,
I have an Excel 'Data' sheet that gets data from Access query (DataImport DataNew db query, then feeds data into 'Model' sheet that setup with multiple formulas for analysis. Everything thus far works fine. I just need to set up a macro to refresh the 'Data' sheet every time Access query gets updated. Currently it refreshes when the user clicks on 'enable refresh when workbook opens' but I would like to set up a button to do that because the user might click on disable refresh....In addition to that I would like the button/macro to refresh the 'Model' sheet with the new data as well. It seems simple, but any direction will be greatly appreciated (Macro or VBA). Thanks. -- when u change the way u look @ things, the things u look at change. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to refresh data
Sahafi, record a macro (Tools / Macros / Record New Macro) when you refresh
and that will give you the code & syntax for refreshing. Then you can call that code whenever you want the data updated. Bill "sahafi" wrote: Hi All, I have an Excel 'Data' sheet that gets data from Access query (DataImport DataNew db query, then feeds data into 'Model' sheet that setup with multiple formulas for analysis. Everything thus far works fine. I just need to set up a macro to refresh the 'Data' sheet every time Access query gets updated. Currently it refreshes when the user clicks on 'enable refresh when workbook opens' but I would like to set up a button to do that because the user might click on disable refresh....In addition to that I would like the button/macro to refresh the 'Model' sheet with the new data as well. It seems simple, but any direction will be greatly appreciated (Macro or VBA). Thanks. -- when u change the way u look @ things, the things u look at change. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to refresh data
Bill,
I have done that, but for some reason whenever I refresh the 'Data' sheet the formulas on my 'Model' sheet turn into '#N/A'. Upon checking that I have found that when I added a row of data to both of my tables in Access, the query get updated, and Excel input the data correctly. The imported data goes into 8 columns, plus I have 2 more columns (I and J) set up via a Vlookup formulas (IF(ISNA(VLookup(---))). My original data up to row 1657, the new row got inserted into row 1658, but on my I & J columns it shows on the Vlookup as 1659 instead of 1658. So every time I do a refresh, I have to change the corresponding criteria in my SUMPRODUCT formula to 1 less row number to match the rest of the criteria on columns (A-H). How can I fix this? Thanks. -- when u change the way u look @ things, the things u look at change. "Bill Pfister" wrote: Sahafi, record a macro (Tools / Macros / Record New Macro) when you refresh and that will give you the code & syntax for refreshing. Then you can call that code whenever you want the data updated. Bill "sahafi" wrote: Hi All, I have an Excel 'Data' sheet that gets data from Access query (DataImport DataNew db query, then feeds data into 'Model' sheet that setup with multiple formulas for analysis. Everything thus far works fine. I just need to set up a macro to refresh the 'Data' sheet every time Access query gets updated. Currently it refreshes when the user clicks on 'enable refresh when workbook opens' but I would like to set up a button to do that because the user might click on disable refresh....In addition to that I would like the button/macro to refresh the 'Model' sheet with the new data as well. It seems simple, but any direction will be greatly appreciated (Macro or VBA). Thanks. -- when u change the way u look @ things, the things u look at change. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to refresh data
You can use either the indirect or offset functions to maintain the proper
cell references. In most cases, Offset will be better. With offset, you specify the first cell (the absolute reference) and then specify the number of rows to count down or over. You can use the Row() function to determine how many rows to offset (Row() - Row( first cell of data block) = row offset). "sahafi" wrote: Bill, I have done that, but for some reason whenever I refresh the 'Data' sheet the formulas on my 'Model' sheet turn into '#N/A'. Upon checking that I have found that when I added a row of data to both of my tables in Access, the query get updated, and Excel input the data correctly. The imported data goes into 8 columns, plus I have 2 more columns (I and J) set up via a Vlookup formulas (IF(ISNA(VLookup(---))). My original data up to row 1657, the new row got inserted into row 1658, but on my I & J columns it shows on the Vlookup as 1659 instead of 1658. So every time I do a refresh, I have to change the corresponding criteria in my SUMPRODUCT formula to 1 less row number to match the rest of the criteria on columns (A-H). How can I fix this? Thanks. -- when u change the way u look @ things, the things u look at change. "Bill Pfister" wrote: Sahafi, record a macro (Tools / Macros / Record New Macro) when you refresh and that will give you the code & syntax for refreshing. Then you can call that code whenever you want the data updated. Bill "sahafi" wrote: Hi All, I have an Excel 'Data' sheet that gets data from Access query (DataImport DataNew db query, then feeds data into 'Model' sheet that setup with multiple formulas for analysis. Everything thus far works fine. I just need to set up a macro to refresh the 'Data' sheet every time Access query gets updated. Currently it refreshes when the user clicks on 'enable refresh when workbook opens' but I would like to set up a button to do that because the user might click on disable refresh....In addition to that I would like the button/macro to refresh the 'Model' sheet with the new data as well. It seems simple, but any direction will be greatly appreciated (Macro or VBA). Thanks. -- when u change the way u look @ things, the things u look at change. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Open the Menu Data,Refresh Data,filename,import | Excel Programming | |||
Macro to Refresh Data & Print | Excel Programming | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Run Macro on External Data Refresh | Excel Programming | |||
Run Macro on External Data Refresh | Excel Programming |