How to export queries results into a single excel sheet as tables

[vannessa]

Public Sub gets(pCity As String, pWeek As Byte)
    'export function
    'Exports tables in access database to Excel
    'Referenec to DAO is required
    Dim strExcelFile As String
    Dim strWorksheet As String
    Dim strDB As String
    Dim strTable As String
    Dim strTable1 As String
    Dim strTable2 As String
    Dim strTable3 As String
    Dim strTable4 As String
    Dim strTable5 As String
    Dim strTable6 As String
    Dim objDB As Database
    Dim outputFileName As String
 
    'Change Based on your needs or use parameters as sub
    strExcelFile = "C:\Documents and Settings\velal\My Documents\Book1.xls"
    strWorksheet = "Council"
    strDB = "C:\Documents and Settings\My Documents\Completed.mdb"
    strTable = "stud"
    strTable1 = "attendance"
    strTable2 = "marks"
    strTable3 = "fee"
    strTable4 = "course"
    strTable5 = "subject"
    strTable6 = "PD"
    Set objDB = OpenDatabase(strDB)
    'If excel file already exist you can delete it here
    If Dir(strExcelFile) <> "" Then Kill strExcelFile
    objDB.Execute ("SELECT * INTO [Excel 8.0; DATABASE=" & _
        strExcelFile & "].[" & strWorksheet & "] FROM " & "[" & strTable & "]" & _
        "WHERE city = '" & pCity & "' AND [Week Periodtext] = " & pWeek)
        outputFileName = "C:\Documents and Settings\velal\My Documents\Book1.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "stud", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "attendance", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "marks", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "fee", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "course", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "subject", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PD", outputFileName, True
    objDB.Close
    Set objDB = Nothing
End Sub
' Start here with parameters provided
Public Sub start()
    Call gets("Brisbane", 5)
End Sub
 

[MMcCarthy]

Vanessa

You haven't actually asked a question, just posted a load of code. Where exactly are you having a problem? What part of your code does not work?

Mary

[vannessa]

hi mary,
With th ecode i'm having i can import all the seven tables into seven spreadsheets of the same workbook. But now i want to make all the seven spreadsheets into one spreadsheet with all the tables in 2 columns and 3 rows. Is that possible