Hi all,
well i am tryin to write 6 reports into 1 excel workbook with 6 different tabs, i have written this particualr code to function at a button click
Now the problem is that i am able to write into the excel workbook and get the 6 different tabs, but i am not able to open up the excel sheet automatically once the workbook has been created, i am very new to the programming environment and would like to get some serious help on this. Please advice
well i am tryin to write 6 reports into 1 excel workbook with 6 different tabs, i have written this particualr code to function at a button click
Code:
Dim strExcelFile As String
Dim strWorksheet, strWorksheet1, strWorksheet2, strWorksheet3, strWorksheet4, strWorsheet5 As String
Dim strDB As String
Dim strTable, strTable1, strTable2, strTable3, strTable4, strTable5 As String
Dim objDB As Database
Dim a As String
a = Application.CurrentProject.Path
'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "C:\Sales.xls"
strWorksheet = "FINAL_TEST"
strWorksheet1 = "FINAL_TEST_UAE"
strWorksheet2 = "FINAL_TEST_KUWAIT"
strWorksheet3 = "FINAL_TEST_QATAR"
strWorksheet4 = "FINAL_TEST_OMAN"
strWorksheet5 = "FINAL_TEST_BAHRAIN"
strDB = a & "\Automation.mdb"
strTable = "FINAL_TEST"
strTable1 = "FINAL_TEST_UAE"
strTable2 = "FINAL_TEST_KUWAIT"
strTable3 = "FINAL_TEST_QATAR"
strTable4 = "FINAL_TEST_OMAN"
strTable5 = "FINAL_TEST_BAHRAIN"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet1 & "] FROM " & "[" & strTable1 & "]"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet2 & "] FROM " & "[" & strTable2 & "]"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet3 & "] FROM " & "[" & strTable3 & "]"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet4 & "] FROM " & "[" & strTable4 & "]"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet5 & "] FROM " & "[" & strTable5 & "]"
objDB.Close
Set objDB = Nothing
MsgBox "Sales-Reports have been succefully created"Now the problem is that i am able to write into the excel workbook and get the 6 different tabs, but i am not able to open up the excel sheet automatically once the workbook has been created, i am very new to the programming environment and would like to get some serious help on this. Please advice
