I need some help with export multiple queries into one excel Workbook, but, multiple Worksheet? using the criteria from a table in MS Access VBA
ATTACHED IS DB for Reference.
Table name: Tbl_Final (columns listed below)
- System
- User ID
- User Type
- Status
- Job Position
Based on the Unique values in the column "System" in table "Tbl_Final" (SQL query below), I need to create INDIVIDUAL excel files and export it to folder. Example: SELECT TBL_FINAL.System, TBL_FINAL.[User ID], TBL_FINAL.[User Type], TBL_FINAL.Status, TBL_FINAL.[Job Position] FROM TBL_FINAL WHERE (((TBL_FINAL.System)="OS/400"));
SELECT TBL_FINAL.System, TBL_FINAL.[User ID], TBL_FINAL.[User Type], TBL_FINAL.Status, TBL_FINAL.[Job Position]
FROM TBL_FINAL
WHERE (((TBL_FINAL.System)="Tab"));
After googling, i managed to find a code which matches the criterion. But encountring some hurdles :(
Request for some help !!
=================================== Option Compare Database
Private Sub Command1_Click()
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
strQry = "REPORT_QUERY"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef(strQry)
strSQL = "SELECT System, [User ID], [User Type], [Status] FROM TBL_FINAL WHERE System = 'OS/400'"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, _
strQry, "C:\Program Files\Export\GENERAL_EXPORT.xls", True, _
"Sheet1"
strSQL = "SELECT System, [User ID], [User Type], [Status] FROM TBL_FINAL WHERE System = 'MySys'"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, _
strQry, "C:\Program Files\Export\GENERAL_EXPORT.xls", True, _
"Sheet2"
DoCmd.DeleteObject acQuery, strQry