473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 11223
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,556 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,556 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

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

Similar topics

1
by: RichpU | last post by:
Hi Everyone, I am trying to export a group of tables to an excel workbook and I would like to export table 1 to sheet 1, table 2 to sheet 2 etc. I am using <%Response.ContentType =...
3
by: mukeshsrivastav | last post by:
dear sir i want to move form excel to access. i have 5 excel file having same formats and fields.now i want to import all data in one access table. importing one file is easy .but importing and...
1
by: msanger | last post by:
Hi, I have several excel sheets in Office 2007, which has all kinds of standard calculations, and a consolidated Excel Sheet with totals and rollup of all the other excel files. The issue I am...
18
by: Landaverry | last post by:
I have an excel file that has over 20 tabs. I need to import all tabs into one table and am having trouble finding code to do this. I would liike the user to be able to press on button, navigate to...
1
by: Rohan | last post by:
Hello, I would like to write a script which does the following job. Take column1 and 7 from 10 different excel sheets and pasthe them into a new excel worksheet. Any ideas on how to do it Thanks,
1
by: DThreadgill | last post by:
I have a spreadsheet with about 35 tabs that I need to import into one table in Access. I found this link http://www.thescripts.com/forum/thread650662-import+multiple+tabs.html and changed some...
7
by: ivancycheng | last post by:
I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm...
2
by: libish | last post by:
hi all, can any one help me to create multiple instance of a thread? here what i'm doing is that , i want to upload a file from my c# windows application, once i put it for upload i want to...
0
by: appu123 | last post by:
How can we copy multiple Excel Sheets into a new Workbook. I was trying to copy using File::Copy, but it does not help as it overwrites the existing details
1
by: Sekhar C | last post by:
I have 100 excel sheets with same field name,with same number of fields,with similar datatype. i want to transfer data from all excel sheets to one Sql database Table using one Package, i am using...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.