[RESOLVED] VB6 Add Excel sheet to workbook if sheet doesn't exist already

[Bezzie]

I need to create a new Excel workbook from within VB6 program and then write to three different sheets in the workbook.

On my version of Excel it opens a new workbook with three default sheets namely Sheet1, Sheet2 and Sheet3, so I can rename the sheets and write to them. Works without a problem.

On another users Excel it opens a new workbook with only one default sheet namely Sheet1, so trying to write to the non existing sheets obviously errors.

How do I check if the three sheets exist upon creating/opening the workbook and add them if they don't already exist?

[Arnoutdv]

The Workbook has a property .Sheets.Count this can be used to check how many worksheets you have to add.

[Bezzie]

Thanks. Got that sorted now.

Just another question: When the program erred for instance because there is not three sheets available for writing it goes to the error handler and shows the error message. I then want to exit the sub after closing the Excel instance but it completely locks up my program. I try to close with the following:

oXLBook.close
Set oXLBook = Nothing
oXLApp.Quit
Set oXLApp = Nothing
Exit Sub

I have to go to windows Task Manager and end the process there to get the program to close

Any ideas I can try to prevent the program locking up when some Excel error occurs?

[Zvoni]

When you close your Workbook --> with or without saving changes (if the WB is "dirty")?

[Bezzie]

Without saving. Only saves the workbook once data is written. This crash happened just after creating and opening the workbook when I tried to rename the three sheets I'm going to use. The crash occurred on trying to rename the sheets that doesn't exist (this has been sorted out now so the three sheets are there) but I still would like to know why it locks up the program so badly.

[westconn1]

as you do not specify where in your code the lock up happens it is hard to guess, but as suggested by zvoni excel is presenting a message to ask if you want to save changes, but as you do not see it you do not respond, tell it you do not want to save changes

oXLBook.close false

[Bezzie]

The lockup happens when trying to rename a sheet that doesn't exist. On my PC Excel opens a workbook with three default worksheets so I renamed the three worksheets to what I need. On another users PC Excel only opens with one default worksheet and the program tried to rename three worksheets, two of which doesn't exist. It then errors and goes to the error handler where I do the above but couldn't exit the program once the error occurred. Will change my close and see how it then goes.

Thanks for the input.

[Arnoutdv]

How do you rename the sheets?
You can address them by the index.

Do While oWB.Sheets.Count < 3
  oWB.Sheets.Add
Loop

For i = 1 To 3
  oWB.Sheets(i).Name = "VBForums " & CStr(i)
Next i

[westconn1]

Any ideas I can try to prevent the program locking up when some Excel error occurs?

you need to handle any error that may occur

[Zvoni]

as you do not specify where in your code the lock up happens it is hard to guess, but as suggested by zvoni excel is presenting a message to ask if you want to save changes, but as you do not see it you do not respond, tell it you do not want to save changes

oXLBook.close false

Exactly what i was alluding to.
The OP says his code jumps to the error-handler, meaning the error itself (renaming missing worksheet) is not the cause for the Lock-up.
He closes his WB (and misses/ignores the "Do you want so save changes?"-Message).
His code steps to the next line which tries to quit the Excel-App, which is not possible, since it's still waiting for an answer --> Deadlock

I'm pretty sure, his workbook is "dirty", since it has at least one sheet he probably renamed succesfully (failing at the second sheet)

[Bezzie]

I rename them with:

oXLBook.Worksheets(1).Name = "New Name"

Adding the False to the oXLBook.Close sorted that problem of the lockup. Problem was a Save As from Excel that sat behind my program.

Thanks for the help.

[Zvoni]

I rename them with:

oXLBook.Worksheets(1).Name = "New Name"

Adding the False to the oXLBook.Close sorted that problem of the lockup. Problem was a Save As from Excel that sat behind my program.

Thanks for the help.

Exactly what i and pete were telling you