Button to export query to excel

[Carol via AccessMonster.com]

I have a command button on a form. It runs the query just fine.

1. But I also need it to after running the query, export the query to excel
to the c:\documents and settings
2. To also be able to run the next query and export to same excel file but a
different sheet and name the sheet tab.. instead of sheet1 it would be mgrs2

here's my code so far to run the query:

Private Sub Querymgrs_Click()
On Error GoTo Err_Querymgrs_Click

Dim stDocName As String

stDocName = "Querymgrs"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Querymgrs_Click:
Exit Sub

Err_Querymgrs_Click:
MsgBox Err.Description
Resume Exit_Querymgrs_Click

End Sub

 

[Ken Snell [MVP]]

See the examples on my website:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

 

[Carol via AccessMonster.com]

Ken,
Your examples are great. I'm using the one to write a query to Excel and
take the firs 10 letters of the query as the sheet name.
How do I run multiple queries one at a time and have them added the same
Excel spreadsheet with a different name for each sheet?

Thanks,
Carol

See the examples on my website:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

I have a command button on a form. It runs the query just fine.

[quoted text clipped - 23 lines]

End Sub

 

[Ken Snell [MVP]]

If you keep the filename for the EXCEL file the same for each query, then
you just need to loop through all your queries and export them to the same
EXCEL file. You don't need to "run" each query, just export each query one
at a time.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken,
Your examples are great. I'm using the one to write a query to Excel and
take the firs 10 letters of the query as the sheet name.
How do I run multiple queries one at a time and have them added the same
Excel spreadsheet with a different name for each sheet?

Thanks,
Carol

See the examples on my website:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

I have a command button on a form. It runs the query just fine.

[quoted text clipped - 23 lines]

End Sub

 

[Carol via AccessMonster.com]

Thank You!!

If you keep the filename for the EXCEL file the same for each query, then
you just need to loop through all your queries and export them to the same
EXCEL file. You don't need to "run" each query, just export each query one
at a time.

Ken,
Your examples are great. I'm using the one to write a query to Excel and

[quoted text clipped - 13 lines]

 

[Carol via AccessMonster.com]

Hi Ken,
I have one problem that is probably quick fix. Below I'm running several
queries
to export into one workbook with separate sheets. It's working like a charm,
but
I want to use your code to take the first 10 letters to name the sheets.
This is where
I am stuck. Here's my code below:

DoCmd.TransferSpreadsheet acExport, 9, "OPkeys_IkeyMst_matchIn Keys", "C:\
Export\Keys.xls", False, "OPkeys_IkeyMst_matchIn Keys", xls.Name = Trim(Left
(strRecordsetDataSource, 10))

Thanks,
Carol

Thank You!!

If you keep the filename for the EXCEL file the same for each query, then
you just need to loop through all your queries and export them to the same

[quoted text clipped - 6 lines]

 

[Ken Snell [MVP]]

The DoCmd.TransferSpreadsheet code step line needs to be this (all one line,
watch for linewrapping in newsreader):

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"OPkeys_IkeyMst_matchIn Keys","C:\Export\Keys.xls",,
Trim(Left(strRecordsetDataSource, 10))


Note my article about using the Range argument for exporting:
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

There can be some quirks when you use this approach.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,
I have one problem that is probably quick fix. Below I'm running several
queries
to export into one workbook with separate sheets. It's working like a
charm,
but
I want to use your code to take the first 10 letters to name the sheets.
This is where
I am stuck. Here's my code below:

DoCmd.TransferSpreadsheet acExport, 9, "OPkeys_IkeyMst_matchIn Keys", "C:\
Export\Keys.xls", False, "OPkeys_IkeyMst_matchIn Keys", xls.Name =
Trim(Left
(strRecordsetDataSource, 10))

Thanks,
Carol

Thank You!!

If you keep the filename for the EXCEL file the same for each query, then
you just need to loop through all your queries and export them to the
same

[quoted text clipped - 6 lines]

End Sub