VB-Excel Interface (also see "Excel Wont Quit" post)

Although it's a small comfort, I see that I'm not the only one who has this problem (ie - "Excel Wont Quit").

I've worn off the ends of my fingers by trying different sequences and methods of my code with no positive results. MSDN has no useful information either (that I could find). Here is my code again (for those who missed it before).


Public Sub NewWorkbook()
Dim xlap As Excel.Application
Dim xlwb As Excel.Workbooks
Dim xlws As Excel.Worksheet


Set xlap = New Excel.Application

xlap.DisplayAlerts = False


'**************************************
' Add new workbook
'**************************************

xlap.SheetsInNewWorkbook = 1
xlap.Workbooks.Add
Set xlwb = xlap.Workbooks


'**************************************
' Name the first worksheet. Then add
' 2 more worksheets and name them
'**************************************

xlap.Worksheets.Item(1).Name = "-51"

xlap.Worksheets.Add After:=Worksheets(1)
xlap.Worksheets.Item(2).Name = "-52"

xlap.Worksheets.Add After:=Worksheets(2)
xlap.Worksheets.Item(3).Name = "-53"


'*************************************
' Write test value to first cell in
' each sheet
'*************************************

Set xlws = xlap.Worksheets("-51")
xlws.Cells(1, 1) = "Test Sheet 1"

Set xlws = xlap.Worksheets("-52")
xlws.Cells(1, 1) = "Test Sheet 2"

Set xlws = xlap.Worksheets("-53")
xlws.Cells(1, 1) = "Test Sheet 3"

'*************************************
' Make the top sheet active
'*************************************

xlwb.Item(1).Worksheets(1).Activate


'*************************************
' Save new workbook and prepare to
' exit
'*************************************

xlap.ActiveWorkbook.SaveAs "C:\test2.xls"
xlap.DisplayAlerts = True
xlwb.Close
xlap.Quit


'*************************************
' Destroy the objects and exit
'*************************************

Set xlwb = Nothing
Set xlws = Nothing
Set xlap = Nothing

End Sub


Simply put - This just doesn't work and it ought to. Maybe I'm dense, but I just don't see it.

Please help !

Fiz

Here is some info that I've found from Microsoft on this problem. This is the link.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q319832&

It appears as if the problem is related to "Early" or "Late" binding. I will continue to study the problem.

Fiz

Continuing---
After reading the article on Microsoft's web site at the link

http://support.microsoft.com/defaul...;EN-US;Q319832&

I have concluded that Microsoft screwed this one up real bad. Any reference to an "unqualified" Office function creates a hidden reference that cannot be destoyed until the calling application closes. Even if you explicitly create and destoy the object in your code.

VB does you a "favor" by creating a Global object that cannot be destroyed when you refer to one of the Office objects.

Microsoft's proposed solution is to remove the references to Excel, Word, etc, then to write code blindly without any help on objects, methods, and enumerated values. This is poor alternative.

Read the article at this link for yourself. If you can find an easier solution out of this mess, please let me know.

Frustrated.

I think you have some wrong references to some objects

Public Sub NewWorkbook()
Dim xlap As Excel.Application
Dim xlwb As Excel.Workbooks
Dim xlws As Excel.Worksheet

Set xlap = New Excel.Application

xlap.DisplayAlerts = False


'**************************************
' Add new workbook
'**************************************

xlap.SheetsInNewWorkbook = 1
xlap.Workbooks.Add
Set xlwb = xlap.Workbooks

'**************************************
' Name the first worksheet. Then add
' 2 more worksheets and name them
'**************************************

' If you are going to access a Worksheet
' you shouldn't do this
' xlap.Worksheets.Item(1).Name = "-51"
' A worksheet is a part of a workbook, which
' belongs to the app, you shouldn't refer
' if it from the xlap object.

With xlwb(1)
.Worksheets(1).Name = "-51"

.Worksheets.Add After:= .Worksheets(1) ' <- you forgot the dot
.Worksheets(2).Name = "-52"

.Worksheets.Add After:= .Worksheets(2) ' <- you forgot the dot
.Worksheets(3).Name = "-53"

End With

'*************************************
' Write test value to first cell in
' each sheet
'*************************************

Set xlws = xlwb(1).Worksheets("-51")
xlws.Cells(1, 1) = "Test Sheet 1"

Set xlws = xlwb(1).Worksheets("-52")
xlws.Cells(1, 1) = "Test Sheet 2"

Set xlws = xlwb(1).Worksheets("-53")
xlws.Cells(1, 1) = "Test Sheet 3"

'*************************************
' Make the top sheet active
'*************************************

xlwb(1).Worksheets(1).Activate

'*************************************
' Save new workbook and prepare to
' exit
'*************************************

xlap.ActiveWorkbook.SaveAs "C:\test2.xls"
xlap.DisplayAlerts = True
xlwb.Close
xlap.Quit


'*************************************
' Destroy the objects and exit
'*************************************

Set xlwb = Nothing
Set xlws = Nothing
Set xlap = Nothing

End Sub

Thank you ArnoutV.

Your changes to the way I was refering to the Worksheet object solved the problem. Apparently, refering to the Worksheet from the xlap object was triggering this hidden Global reference crap. Refering to a worksheet from the workbook object makes more sense anyway.

Thanks again.

The problem was not to that you were refering to the worksheet by the xlap

xlap.Worksheets.Add After:=Worksheets(1)

It was that you were not refering to the worksheet at all
Add After:=Worksheets(1) ' No reference to WorkSheets(1)

See no reference. In ArnoutV's solution he refered to that worksheet using the with statement and the .Worksheets(1)

That is the reason it didn't work.