exporting multiple queries to seperate worksheets in excel

[Andy G]

The subject said it all. I need to export 4 different queries to 4
different worksheets in 1 workbook.

I'm sure it's already been asked but I can't find an answer for the life of
me.

Thanks
andy

 

['69 Camaro]

Hi, Andy.

I need to export 4 different queries to 4 different worksheets in 1 workbook.

Try:

Private Sub ExportToExcelBtn_Click()

On Error GoTo ErrHandler

Dim qryList(4) As String
Dim strSQL As String
Dim sCnxn As String
Dim sPath As String
Dim sFile As String
Dim idx As Long

qryList(1) = "qryEmployeePromotions"
qryList(2) = "qrySalaries"
qryList(3) = "qryRetirements"
qryList(4) = "qryMgrSelectees"
sFile = "EmployeesDec06.xls"
sPath = "C:\Work\"
sCnxn = "[Excel 8.0;HDR=Yes;DATABASE=" & sPath

For idx = 1 To UBound(qryList)
strSQL = "SELECT * INTO " & _
sCnxn & sFile & "]." & qryList(idx) & _
" FROM " & qryList(idx) & ";"
CurrentDb().Execute strSQL, dbFailOnError
Next idx

Erase qryList()

Exit Sub

ErrHandler:

MsgBox "Error in ExportToExcelBtn_Click( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Ken Snell \(MVP\)]

TransferSpreadsheet will export to a new worksheet if you tell it to export
to an existing EXCEL file and there is not a worksheet already in that file
with the query's name. So four, consecutive TransferSpreadsheet actions to
the same file will do that for you.