Need urgent Access Help

By chris.palmer ·
I have created a database to track the workload of mobile vehicle technicians. The report/query is sorted by Zone names ie Zone 1, Zone 2 etc.

I need to output the report/query to excel but have it on seperate worksheets by Zones

Ie - When i open the excel spreadsheet with imported data it will have 7 worksheets named Zone 1 to Zone 7.

How can this be done, i have racked my brains and exhuasted all options of help on other forums.

Please help i need this to produce reports to MD's of the company

Thanks in advance

Chris Palmer

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Lots of Links

by robo_dev In reply to Need urgent Access Help

You will have to use VBA to move your data to an exact worksheet in an Excel spreadsheet.

DoCmd.TransferSpreadsheet acExport, 8, "ReportData", "C:\ReportName.xls"

where ReportData is the linked table name in the local database

Collapse -

VBA code

by arkman In reply to Need urgent Access Help

Hi I don't know where I got this from but it works a treat. I use it a lot so thank you to the source!.
Create a query for each zone from your main table. The following code exports into one workbook with a worksheet for each zone. "txtExportFile" is the full path and name of the Excel file. I have this path in a textbox on my form with a button to run the VBA

Private Sub ExporttoExcel_Click()
On Error GoTo Do_Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Zone1query", txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Zone2query", txtExportFile

MsgBox "The queries have been successfully exported to " & txtExportFile & "."

Exit Sub

MsgBox "Export has failed. An error occurred or the user terminated the operation."

End Sub

Good LucK

Related Discussions

Related Forums