Word-run XL code behaves differently in 2007

[Mark Tangard]

I have a Word macro that launches Excel, grabs spreadsheet records, and
displays them (in a really big font) on a userform's listbox. In 2003
this works fine and Excel closes afterward as intended. In 2007 the same
code leaves Excel running, and this frightens the horses. Any
suggestions? The relevant code from the form is below. Thanks for any
clues. MT

Private Sub ShowML()
Dim XL As Object, XS As Worksheet, DBmaxRows As String, DBrangeAddr As
String
Set XL = GetObject("Z:\Test\MyNiceExcelFile.xls")
Set XS = XL.Sheets(1)
XL.Windows(1).Visible = False
DBmaxRows = XS.UsedRange.Rows.Count
DBrangeAddr = "A2:K" & DBmaxRows
LBX.List = XL.Sheets(1).Range(DBrangeAddr).value
LBX.BoundColumn = 0
XL.Close 0
Set XL = Nothing
Set XS = Nothing
DoEvents
LBX.ListIndex = 0

 

[Pesach Shelnitz]

Hi Mark,

To launch Excel from Word, I suggest that you use the following lines of code.

Const Error_NotRunning = 429
Dim xlApp As Object

Set xlApp = GetObject(, "Excel.Application")
If Err.Number = Error_NotRunning Then
Set xlApp = CreateObject("Excel.Application")
MsgBox "A new instance of Excel was created."
Else
MsgBox "An open instance of Excel is being used."
End If
On Error GoTo 0
xlApp.Visible = True

If you open Excel (or use an open instance of Excel) this way, you will have
an xlApp object on which you can call the Close method.

 

[Cindy M.]

Hi Mark,

I think you need to

set XS = Nothing
Set XL = Nothing

As the worksheet is dependent on the workbook object. If
you still have a hold on the sheet, the workbook object
can't be released, which would likely hold the application
open.

Rule-of-thumb: Always set object to Nothing in the reverse
order in which they're created.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail