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.Application")
    xlObj.Workbooks.Add
    xlObj.Visible = True
    Set rs = CurrentDb.OpenRecordset("POD1")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs  'copy the data
   
    Set rs = CurrentDb.OpenRecordset("POD2")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet2")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
       
    Set rs = CurrentDb.OpenRecordset("POD3")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet3")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
   
    Set rs = CurrentDb.OpenRecordset("POD4")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet4")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
   
    Set rs = CurrentDb.OpenRecordset("POD5")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet5")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
   
    Set rs = CurrentDb.OpenRecordset("POD6")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet6")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
   
   
    'save the excel file
   
   
    xlObj.activeworkbook.saveas stFolder & Format(Date, "yyyymmdd") & "_ClaimAudit.xlsx"
   
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
End Sub

LVL 9
tonydemarcoAsked:
Who is Participating?
 
SimonCommented:
or you could insert a block of code to insert required number of extra sheets.

xlObj.Workbooks.Add '----- existing code
    xlObj.Visible = True'----- existing code
    
    ' -- New block to insert extra worksheets into the Excel workbook
    Const numberOfSheetsToAdd As Integer = 3 
    Dim wb As Object, x As Integer
    Set wb = xlObj.ActiveWorkbook
    For x = 1 To numberOfSheetsToAdd
        wb.Worksheets.Add after:=wb.Worksheets(wb.Worksheets.Count)
    Next
    ' -- End of new block
    
    Set rs = CurrentDb.OpenRecordset("POD1")'----- existing code
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")'----- existing code

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
one way is to set the default to 6 sheets when creating a new workbook
office button> excel options > popular
when creating new workbooks

include this many sheets  6
0
 
Patrick MatthewsCommented:
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
0
 
tonydemarcoAuthor Commented:
Excellent!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.