Export Access Tables to Excel Template

[CorrieAnn]

I have an Access database which has 15 resulting tables. I need to export each of these into their own Excel template.

The templates are pre-formatted and ready to print as is due to the fact that the end users are NOT very computer savvy. Basically, they will have a switchboard where they will be able to select any of the 15 reports (tables) to produce and once they push that button, Access should create the table and export the data straight into a pre-formatted template which is ready for printing without any interaction from them.

An example of a table name would be "WOS Ametex". The data in this table should be exported to a file which resides here: C:\Documents and Settings\cgillen\Desktop\WOS Ametex.xlt. I can't seem to get just the data to come over nor can I stop it from creating it's own sheet tab within that workbook.

Any ideas? This needs to be as "dummy-proof" as possible!

 

[MikeAuz1979]

Hi CorrieAnn,

If I've read this right then the easiest way is to use MS Query on each of the excel templates back to the relevant Access table/Query. Data\Get External\New Database Query

I recently did something like this as follows:

Access Code for Export Button ---
Docmd.runquery "Update tblData"
Set acapp = CreateObject("Excel.Application")
acapp.Visible = True
Const Bookname = "G:\Contract Graphs\Emailed Graphs\ConsumptionModel\ConsumptionEmailer.xls"
acapp.Workbooks.Open (Bookname)
'Run the excel macro
acapp.Run ("CreateOutput")
Access Code End---

With the Excel template having the following code:

Excel Code----

Public Sub CreateOutput()
On Error GoTo ErrHandler:

Application.DisplayAlerts = False

Sheets("Data").Select
'Refresh Site List
Range("I2").Select
'Refresh the MS Querytable
ActiveSheet.QueryTables(3).Refresh (False)
Sheets("ResultsSheet").select

With the print already setup on the excel templates ResultsSheet sheet.