Access ->Excel, how to create a workbook and worksheet

J

Janis

I have this snippet which works to open the named workbook and tab.

Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")
Set wks = appExcel.Worksheets("Employees1")
What I need is to know how to create the workbook and sheet in Access/VBA if
the workbook/sheet do not exist? If possible can you give me an example? I
tried the Create method instead of Open and tried to Add a worksheet named
Employees1 and it didn't work.
tia,
-----------------

Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Create("emp1.xls")

Set wks = appExcel.Worksheets.Add("Employees1")
wks.Activate
 
A

Arvin Meyer [MVP]

Try:

Set wbk = appExcel.Workbooks.Add
' add the sheet and data then
appExcel.SaveAs "C:\MyFolder\emp1.xls"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top