Import data from Access to a Templated Excel Spreadsheet

[jofarrell]

I have a VB program that retrieves basically time sheet data and stores it in an Access 97 database. These numbers are then used for a monthly report in Excel that is sent away to head office. Is there an easy way to set up a formula driven Excel Spreadsheet to populate once a date is given .. or should I try and populate the sheet from VB?

Any suggestions would be greatly appreciated.

Joanne

 

[Rjc8513]

I output an Access query as an Excel file and
then use an Excel template with formula references
to the output file. Works great!

Richard...

 

[jofarrell]

Hi Richard,

Mind if I for a little more detail or where you found out how to do that?


Joanne

 

[Store300]

Joanne,

One way is to create anew module and then a sub routine alone the lines of this:

Sub MyExport()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, [MyQuery], [My Spreadsheet], True

End Sub

Change the [MyQuery] To the name of the query whose results you want to export and change [My Spreadsheet] to the full path and name of the spreadsheet you want it to go to. (The spreadsheet must not be open when you run the macro.
Store300

Store300@ftnetwork.com

 

[jofarrell]

Thank you Store300, thats along the lines of what I was looking for.

Joanne

 

[Store300]

A pleasure. Store300

Store300@ftnetwork.com