Community archive

Ask something
Question Locked

Need urgent Access Help

0 helpful
3 replies

by chris.palmer

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

3 replies

by arkman

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

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

End Sub

Good LucK

This discussion is closed to new replies.