Code:
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
Comment