Transferring Data from Access to Excel

[glamlife]

Hello!

I am trying to set up a relationship between a financial model in Excel and an Access database. Currently I've set up an MS Query to bring my Access data into Excel. But my problem is that the linked sheets which run off this data will need to add or delete rows if the number of records in the database changes. (The financial model should have a row for each record in the database plus a GRAND TOTAL row.)

Is there a straightfoward way for Excel to add or delete rows (copying formulas as necessary & preserving the GRAND TOTAL row)
in a spreadsheet based on the number of records coming from Access? Is it necessary to use VBA?

Any advice would be appreciated.

 

[Russell Hauf]

Hi Glam,

This is actually more of an Excel question. You can define a dynamic range for your data. You can search the board for more examples (type in "Dynamic Range" after you click on Search above, and also click on "find all words" - or whatever it says to that effect).

But as an example, say the sheet in your workbook that contains the MS Query data is called "Data1". In Excel, go to Insert-Name-Define, and type in a name in the box at the top (like rngQueryData). In the "Refers To:" box, type

=OFFSET(Data1!$A$1,,,COUNTA(Data1!$A:$A),COUNTA(Data1!$1:$1))

This assumes that your data is continuous on that sheet (that there is only the data from the query, and that the data starts in A1).

HTH,

Russell
This message was edited by Russell Hauf on 2003-01-20 18:23