Add worksheet to excel

[BSando]

OK I'm sure this is very easy.

I want to open an excel workbook and export data from a query to a new worksheet.

I have been able to work out how to add a new worksheet to the workbook but can not work out how to name the worksheet.

----------------------------------------

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)


Thanks in advance

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[SkipVought]

hi,

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
Activesheet.Name = "Sheet Name"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BSando]

Thanks but what would I add if I wanted the date for example? I have tried :

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = (Format(Date, "dd-mm-yy"))

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[SkipVought]

Works for me!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BSando]

I get this error:

Method 'Worksheets' of object'_Global' failed

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[GummowN]

At what point does the code error??
Is it on the naming or the adding worksheet?
What happens if you try and name it something else?
Have you tried created a string for the name based on your date and passing that as the sheet name?

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[RoyVidar]

Based on the small snippets here (which probably works like a charm within Excel), I think this might be caused by implicit referencing. Have a look at PRB: Excel Automation Fails Second Time Code Runs.

Roy-Vidar

 

[BSando]

Thanks. I had cleared the workbook to nothing.
It works if I want to call the new sheet "sando"

But I still get the error if I try to format the new sheet as a date.

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[BSando]

OK. I have narrowed down the problem.

my code opens the Excel application and adds the new sheet. fine.

When I close the excel application and try to run the code again that's when I get the error.

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[GummowN]

Sorry for replying with questions - but this problem intrigues me.

I am assuming you close the spreadsheet and then reopen it? Why?
Also why are you running the code? Will this not add another sheet called the same as the one you have already added? Post up all your code, and also at what point it errors the second time.

You are not being very clear on where the error occurs which makes it difficult. Write down step by step what you do and why.

Sorry for not giving you an answer!!!!

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[BSando]

Since my last post I have changed my code. The new code works much better. It seems that the old code hangs excel.
The code where I get the errors is:
-----------------------------------------

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook

Set appExcel = Excel.Application
appExcel.Visible = True

Set wkb = appExcel.Workbooks.Open("c:\monthlysales.xls")
ActiveWorkbook.Sheets.Add Before:=Worksheets _(Worksheets.Count)
ActiveSheet.Name = Format(Date, "mmmm")
wkb = Nothing

---------------------------------------------
My New code
---------------------------------------------
Dim appExcel As New Excel.Application
Dim strPath As String
Dim strNewSheetName As String

strPath = "C:\sando.xls"
strSheetName = Format(Date, "mmmm")

appExcel.Visible = False
appExcel.Workbooks.Open strPath
appExcel.ActiveWorkbook.ActiveSheet.Name = strSheetName
appExcel.ActiveWorkbook.Save

appExcel.Quit

--------------------------------------------

does anyone know how to use the tanseferspreadsheet command and select what page it goes to?

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[GummowN]

When you transferspreadsheet the query/table is entered as sheet of the same name - it should make it easy to do something like this for the renaming:

Dim appXL as new excel.application
Dim wbk as workbook
Dim wks as worksheet

appxl.visible = false
appxl.workbooks.open strpath
set wbk = activeworkbook
set wks = wbk.worksheets(strQueryName)
wks.name = strNewName



If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[BSando]

I have changed my coed again . This time it renames the sheet what I want and copys all the data to that sheet.

---------------------------------

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim sql As String

Set db = CurrentDb
sql = "Select * from[Monthly_sales_report]"

db.CreateQueryDef [Forms]![monthly_report]![Month], sql

DoCmd.TransferSpreadsheet acExport, _ acSpreadsheetTypeExcel9, _
[Forms]![monthly_report]![Month], "C:\Monthlysales.xls", True

db.QueryDefs.Delete [Forms]![monthly_report]![Month]

Set db = Nothing

Some people make things happen, some watch while things happen, and some wonder 'What happened?'