Adding Excel Worksheet to existing Workbook using Access 97 Macro

[Mike F via AccessMonster.com]

Does anyone know how (if) you can add worksheet(s) to an existing Excel
workbook using an MS Access 97 macro?

 

[Ken Snell [MVP]]

Not via a macro in ACCESS, no. But via VBA code in ACCESS, yes.

 

[Mike F via AccessMonster.com]

Can you provide sample code?

Not via a macro in ACCESS, no. But via VBA code in ACCESS, yes.

 

[Ken Snell [MVP]]

Some generic code:

Dim xlx As Object, xlw As Object, xls As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets.Add
xls.Name = "NameForNewWorksheet"
Set xls = Nothing
xlw.Close
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing