tonydemarco
asked on
Acess 2007 Export to Excel 2007 Multiple Sheets
The following code (Capricorn1's code from a previous question - thanks) opens an excel spreadsheet and puts data into Sheets1,2 and 3 but stops at Sheet4 because there is no Sheet4 until I create Sheets4, 5 and 6 when the code halts, then continue the interrupted code.
My question is: How do I get Excel 2007 from Access VBA to open up with 6 Sheets to accomodate the data?
'========================= ========== ========== ========== =======
Private Sub Command0_Click()
stFolder = SaveToFolder()
Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer
Set xlObj = CreateObject("Excel.Applic ation")
xlObj.Workbooks.Add
xlObj.Visible = True
Set rs = CurrentDb.OpenRecordset("P OD1")
Set Sheet = xlObj.activeworkbook.Works heets("She et1")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs 'copy the data
Set rs = CurrentDb.OpenRecordset("P OD2")
Set Sheet = xlObj.activeworkbook.Works heets("She et2")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs
Set rs = CurrentDb.OpenRecordset("P OD3")
Set Sheet = xlObj.activeworkbook.Works heets("She et3")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs
Set rs = CurrentDb.OpenRecordset("P OD4")
Set Sheet = xlObj.activeworkbook.Works heets("She et4")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs
Set rs = CurrentDb.OpenRecordset("P OD5")
Set Sheet = xlObj.activeworkbook.Works heets("She et5")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs
Set rs = CurrentDb.OpenRecordset("P OD6")
Set Sheet = xlObj.activeworkbook.Works heets("She et6")
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom Recordset rs
'save the excel file
xlObj.activeworkbook.savea s stFolder & Format(Date, "yyyymmdd") & "_ClaimAudit.xlsx"
Set Sheet = Nothing
xlObj.Quit
Set xlObj = Nothing
End Sub
My question is: How do I get Excel 2007 from Access VBA to open up with 6 Sheets to accomodate the data?
'=========================
Private Sub Command0_Click()
stFolder = SaveToFolder()
Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer
Set xlObj = CreateObject("Excel.Applic
xlObj.Workbooks.Add
xlObj.Visible = True
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
Set rs = CurrentDb.OpenRecordset("P
Set Sheet = xlObj.activeworkbook.Works
For iCol = 0 To rs.Fields.Count - 1
Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
Next
Sheet.range("A2").CopyFrom
'save the excel file
xlObj.activeworkbook.savea
Set Sheet = Nothing
xlObj.Quit
Set xlObj = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Before this line:
xlObj.Workbooks.Add
add:
Dim SheetsInNew As Long
SheetsInNew = xlObj.SheetsInNewWorkbook
xlObj.SheetsInNewWorkbook = 6
Then, before:
xlObj.Quit
put:
xlObj.SheetsInNewWorkbook = SheetsInNew
xlObj.Workbooks.Add
add:
Dim SheetsInNew As Long
SheetsInNew = xlObj.SheetsInNewWorkbook
xlObj.SheetsInNewWorkbook = 6
Then, before:
xlObj.Quit
put:
xlObj.SheetsInNewWorkbook = SheetsInNew
ASKER
Excellent!
office button> excel options > popular
when creating new workbooks
include this many sheets 6