Manipulating Excel

[tEkHEd]

Hi there..

Can anyone give me the syntax to add multiple worksheets to a workbook?

The code that I have creates a new workbook each time it creates a sheet

Dim oExcel As New Excel.Application
Dim oSheet1, oSheet2, oSheet3, oSheet4, oSheet5 as Excel.Worksheet

oExcel.Workbooks.Add.Worksheets(5)

and tried

oExcel.ActiveWorkbook.Worksheets.Add(1,5,5,)

And tried

oExcel.Sheets.Add(5)

All these bomb out on me either for referencing nothing (I think the (5) means worksheet 5??) and the final example gives me a "System.Runtime.InteropServices.COMException" error: Exception form HRESULT: 0x800A03EC

any help is gratefully appreciated..

Ta

 

[T0AD]

Hi tEkHEd,

If you just want to add 5 worksheets to the workbook try this:

Dim oExcel As New Excel.Application

Do Until oExcel.Workbooks(1).Worksheets.Count = 5
    Application.DoEvents
    oExcel.Workbooks(1).Worksheets.Add
Loop

Let me know if it doesn't work. (Might need to change the 1's to 0's.

T0AD

There's a thin line between genius, and insanity!