exporting multiple queries to seperate worksheets in excel

  • Thread starter Thread starter Andy G
  • Start date Start date
A

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
 
Hi, Andy.
I need to export 4 different queries to 4 different worksheets in 1 workbook.
Click to expand...

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.
 
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.
 
Back
Top