Creating multiple Excel sheets (same workbook) from Access

[James Bowyer]

I am using the following code to create an excel sheet:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
ExcelSheet.Application.Cells(1, 1).Value = "Customer"
ExcelSheet.Application.Cells(1, 2).Value = [CustomerName]
'Again repeated lots of times for various different values

ExcelSheet.Application.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


ExcelSheet.SaveAs mdrive & mdir & "\" & [CustomerName] & "\" & [JobSiteName] & " - " & [JobNumber] & "\a. Site Details\SiteDetails.xlsx"
ExcelSheet.Application.Quit

Set ExcelSheet = Nothing

Which works fine. However, I need more than one sheet to be created. I have tried changing the Excel.Sheet object variable to various other things (including workbook, ect) but they all don't work.

Any ideas how to get more than 1 sheet to be created in the same document?

Thanks!

[Stewart Ross]

You should really have an Excel Application object open, rather than a Worksheet object only, like this:

Dim objExcel as Object
Dim ExcelSheet as Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add (xlWBATWorksheet)
Set ExcelSheet = ObjExcel.ActiveWorksheet

You could then replace your references to ExcelSheet.Appl ication with references to ObjExcel instead.

You can add new worksheets either before or after a specified worksheet in your workbook. Here is an example of adding a worksheet at the end of the workbook:

With objExcel
    .ActiveWorkbook.Worksheets.Add After := .Worksheets(.Worksheets.Count)
End With

You could place this in a separate sub and call it as often as necessary to add individual worksheets, or within a loop if you want to add a specified number of worksheets in one go.

If you do not want to instantiate an Excel Application object you might try using the worksheet object's application property instead, like this:

With ExcelSheet.Application
    .ActiveWorkbook.Worksheets.Add After := .Worksheets(.Worksheets.Count)
End With

It is here that you see the use of With, as it saves an awful lot of references to ExcelSheet.Appl ication throughout the short exemplar.

-Stewart

[James Bowyer]

I got Run time error 438: object doesn't support this property or method"

Using it in this way:

Private Sub Command0_Click()

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
ExcelSheet.Application.Cells(1, 1).Value = "Customer"

With ExcelSheet
    .ActiveWorkbook.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
ExcelSheet.Application.Cells(1, 1).Value = "Customer page 2"

'Again repeated lots of times for various different values

ExcelSheet.Application.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


ExcelSheet.SaveAs "C:\Temp\SiteDetails.xlsx"
ExcelSheet.Application.Quit

Set ExcelSheet = Nothing


End Sub

What am I doing wrong? (it was the .activeworkbook .worksheets etc that was highlighted by vba)

[Stewart Ross]

You have to use the application property of the worksheet object if you want to avoid using a separate application object - and you've missed that out in your version above. Line 9 should read:

With ExcelSheet.Application

-Stewart

[NeoPa]

Application Automation gives some of the basics for working with other (generally MS Office) applications from VBA. It may help you understand the concepts a little better.

[NeoPa]

Another related point to make (though probably of little interest in this particular case) is that it's possible to export multiple queries into their own distinct worksheets all in a single Excel workbook. DoCmd.TransferS preadsheet has a FileName parameter and whenever the value passed matches an existing workbook the new data is added in a a separate worksheet.