Macro to refresh data

[sahafi]

Hi All,

I have an Excel 'Data' sheet that gets data from Access query (Data>>Import
Data>>New 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.

 

[Bill Pfister]

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]

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.

 

[Bill Pfister]

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).