Exporting Query Results to Mulitiple Sheets in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can export my query into excel just fine. My problem is trying to get data
from one query into multiple worksheets within a single workbook. for
example:

Location: (TX, OK, AR, etc...)

I am trying to get all record for TX into a tab called "TX". Short of
creating some sort of control in access, or defining a query in excel, I do
not know how to parse my data. Does anyone have any ideas?
 
Something like this will do it. This assumes there's a second query
which just gets the data from the one you want to export, e.g.
SELECT * FROM MyQuery;



Sub ExportByState(State As String, Destination As String)
Const EXPORT_QUERY = "qryTempExport"
Dim Q As DAO.QueryDef
Dim RememberSQL As String

With CurrentDb.QueryDefs(EXPORT_QUERY)
RememberSQL = .SQL
'remove any trailing Cr or Lf and terminal semicolon
Do While InStr(";" & vbCrLf, Right(.SQL, 1)) > 0
.SQL = Left(.SQL, Len(.SQL) - 1)
Loop
'add WHERE clause to query
.SQL = .SQL & " WHERE State='" & State & "';"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
EXPORT_QUERY, Destination, True, State

'restore original query
.SQL = RememberSQL
End With

End Sub
 
Back
Top