Exporting Queries to Various worksheets

[khwaja]

I have to be able to provide a user the option to export info from a number of queries to a single spreasheet (each query result on a different worksheet). What is the best approach to achieve this?

 

[ghudson]

http://msdn.microsoft.com/en-us/library/bb214134(office.12).aspx

Use the transfershpreadsheet function. Each query will output to a new worksheet. The worksheet will be named the name of the query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourQuery1", "\\Server\Partition\Directory\YourFileName.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourQuery2", "\\Server\Partition\Directory\YourFileName.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourQuery3", "\\Server\Partition\Directory\YourFileName.xls", True

 

[khwaja]

Much appreciate your help.

 

[khwaja]

May I ask you a slight improvement. I might like to retain the copy of spreadsheet for record. So is it possible to somehow add date/time stamping as part of file name and to ensure that files does not get deleted. I am using a statement similar to the following based on your kind help.

Sub weeklyreportdata()
Dim strfileto As String
On Error GoTo E_Handle

strfileto = "\\Nnorsogfas031\Design\99 AK Files\DCE Weekly Reports\Access Queries\WeeklyData.xls"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySavingTargetFy11", strfileto, True

.....

 

[ghudson]

May I ask you a slight improvement. I might like to retain the copy of spreadsheet for record. So is it possible to somehow add date/time stamping as part of file name and to ensure that files does not get deleted. I am using a statement similar to the following based on your kind help.

Sub weeklyreportdata()
Dim strfileto As String
On Error GoTo E_Handle

strfileto = "\\Nnorsogfas031\Design\99 AK Files\DCE Weekly Reports\Access Queries\WeeklyData.xls"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySavingTargetFy11", strfileto, True

.....

To add the date time to the file name I would use something like this...

dim sPath as string
dim sFile as string
dim sDateTime as string
dim sExtension as string
dim sPathFile as string

sPath  = "\\Nnorsogfas031\Design\99 AK Files\DCE Weekly Reports\Access Queries\"
sFile = "WeeklyData_"
sDateTime = format(Now(),"yyyymmddhhnnss")
sExtension = ".xls"
sPathFile = sPath  & sFile & sDateTime & sExtension 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySavingTargetFy11", sPathFile , True

 

[khwaja]

Many thanks for your help.