Link to home
Start Free TrialLog in
Avatar of tonydemarco
tonydemarcoFlag for United States of America

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

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of tonydemarco

ASKER

Excellent!