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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.