VB6 -- Excel Worksheets Assignment

[Nelson Chang]

Hi,
I met an "type mismatch" error with the statement "Set ws =
oXLWBook.Worksheets" in VB6. Have anyone know the correct syntax?
Thanks
=====================================
Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet
Dim ws As Excel.Worksheets
Set oXLApp = CreateObject("Excel.Application")
Set oXLWBook = Workbooks.Add
Set ws = oXLWBook.Worksheets
For Each s In ws
Worksheets(s).Delete
Next s
=====================================


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Robin Hammond]

Looks like you are trying to set an object to a collection.

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim nCounter as Integer
Set oXLApp = CreateObject("Excel.Application")
Set oXLWBook = Workbooks.Add

'haven't tested this line but you will need to turn off alerts
oXLApp.DisplayAlerts = False
For ncounter = oXLWBook.Sheets.Count To 2 Step -1
oXLWBook.Sheets(ncounter).Delete
Next ncounter
oXLApp.DisplayAlerts = True
End Sub

You can't delete the last sheet by the way, which is why I did it this way.

Robin Hammond
www.enhanceddatasystems.com

 

[Alan Beban]

The following seems to work, except that it won't (can't) delete the
final sheet; one must remain open.

Sub test3001()
Dim oXLWBook As Excel.Workbook
Dim ws As Variant
Dim s As Variant
Set oXLWBook = Workbooks.Add
Set ws = Worksheets
Apolication.DisplayAlerts = False
For Each s In ws
s.Delete
Next s
Application.DisplayAlerts = True
End Sub

Alan Beban