469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Creating multiple Excel sheets (same workbook) from Access

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

Expand|Select|Wrap|Line Numbers
  1. Dim ExcelSheet As Object
  2. Set ExcelSheet = CreateObject("Excel.Sheet")
  3. ExcelSheet.Application.Visible = True
  4. ExcelSheet.Application.Cells(1, 1).Value = "Customer"
  5. ExcelSheet.Application.Cells(1, 2).Value = [CustomerName]
  6. 'Again repeated lots of times for various different values
  7.  
  8. ExcelSheet.Application.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  9.  
  10.  
  11. ExcelSheet.SaveAs mdrive & mdir & "\" & [CustomerName] & "\" & [JobSiteName] & " - " & [JobNumber] & "\a. Site Details\SiteDetails.xlsx"
  12. ExcelSheet.Application.Quit
  13.  
  14. Set ExcelSheet = Nothing
  15.  
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!
Sep 4 '11 #1
5 10433
Stewart Ross
2,545 Expert Mod 2GB
You should really have an Excel Application object open, rather than a Worksheet object only, like this:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Object
  2. Dim ExcelSheet as Object
  3. Set objExcel = CreateObject("Excel.Application")
  4. objExcel.Workbooks.Add (xlWBATWorksheet)
  5. Set ExcelSheet = ObjExcel.ActiveWorksheet
You could then replace your references to ExcelSheet.Application 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:

Expand|Select|Wrap|Line Numbers
  1. With objExcel
  2.     .ActiveWorkbook.Worksheets.Add After := .Worksheets(.Worksheets.Count)
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. With ExcelSheet.Application
  2.     .ActiveWorkbook.Worksheets.Add After := .Worksheets(.Worksheets.Count)
  3. End With
It is here that you see the use of With, as it saves an awful lot of references to ExcelSheet.Application throughout the short exemplar.

-Stewart
Sep 4 '11 #2
I got Run time error 438: object doesn't support this property or method"

Using it in this way:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command0_Click()
  3.  
  4. Dim ExcelSheet As Object
  5. Set ExcelSheet = CreateObject("Excel.Sheet")
  6. ExcelSheet.Application.Visible = True
  7. ExcelSheet.Application.Cells(1, 1).Value = "Customer"
  8.  
  9. With ExcelSheet
  10.     .ActiveWorkbook.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
  11. End With
  12. ExcelSheet.Application.Cells(1, 1).Value = "Customer page 2"
  13.  
  14. 'Again repeated lots of times for various different values
  15.  
  16. ExcelSheet.Application.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  17.  
  18.  
  19. ExcelSheet.SaveAs "C:\Temp\SiteDetails.xlsx"
  20. ExcelSheet.Application.Quit
  21.  
  22. Set ExcelSheet = Nothing
  23.  
  24.  
  25. End Sub
  26.  
What am I doing wrong? (it was the .activeworkbook.worksheets etc that was highlighted by vba)
Sep 4 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. With ExcelSheet.Application
-Stewart
Sep 4 '11 #4
NeoPa
32,173 Expert Mod 16PB
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.
Sep 5 '11 #5
NeoPa
32,173 Expert Mod 16PB
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.TransferSpreadsheet has a FileName parameter and whenever the value passed matches an existing workbook the new data is added in a a separate worksheet.
Sep 5 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Rohan | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.