Problem automating Excel from Word

[Mark Tangard]

I have a userform that automates Excel from Word. That's not my strong
suit, but code that has worked fine in Office 2003 is messing up in
2007. The form just brings up a ListBox of data from the Excel sheet.
Relevant code is:

Dim XL As Excel.Application, XS As Excel.Worksheet
Set XL = GetObject("C:\Stuff\MyFile.xls")
XL.Windows(1).Visible = False
Set XS = XL.Sheets(1)
XL.Windows(1).Visible = False
<---code here fills a ListBox with some data from the Excel sheet
XL.Close 0
Set XL = Nothing
Set XS = Nothing

The code runs in both 2003 and 2007, but in 2007 it leaves an instance
of Excel running, which doesn't happen in 2003. How can I get Excel to
close? Grateful for any clues. WinXP SP3.

MT

 

[Doug Robbins - Word MVP]

You should use:

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Set xlApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
End If
With xlApp
Set xlbook = .Workbooks.Open(("C:\Stuff\MyFile.xls")
'Do stuff with xlbook
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If ExcelWasNotRunning Then
xlApp.Quit
End If
Set xlApp = Nothing


--
Hope this helps

Doug Robbins - Word MVP
Please reply only to the newsgroups unless you wish to avail yourself of my
services on a paid, professional basis.

 

[Mark Tangard]

Doug,

This looked really promising, but I get Runtime error 429: ActiveX
component cannot create object.
(It trips at the GetObject line.) Happens in both Office versions.

Any idea what's wrong?

TIA
Mark

You should use:

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Set xlApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
End If
With xlApp
Set xlbook = .Workbooks.Open(("C:\Stuff\MyFile.xls")
'Do stuff with xlbook
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If ExcelWasNotRunning Then
xlApp.Quit
End If
Set xlApp = Nothing

-- Hope this helps Doug Robbins - Word MVP Please reply only to the
newsgroups unless you wish to avail yourself of my services on a paid,

 

[Doug Robbins - Word MVP]

As a result of cutting and pasting only bits of the code to incorporate your
situation, I omitted an essential line of code

On Error Resume Next

The full code should have been

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
End If
With xlApp
Set xlbook = .Workbooks.Open(("C:\Stuff\MyFile.xls")
'Do stuff with xlbook
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If ExcelWasNotRunning Then
xlApp.Quit
End If
Set xlApp = Nothing

--
Hope this helps

Doug Robbins - Word MVP
Please reply only to the newsgroups unless you wish to avail yourself of my
services on a paid, professional basis.

 

[Doug Robbins - Word MVP]

I am not aware of a list though I do not think that all that much has
changed - at least there is not much that has been deleted, though there was
something I came across recently that was no longer supported, but I do not
remember what it was at the moment, so it was probably something pretty
obscure.

In the case of this particular code, the method has been contained in the
article How to Control Excel from Word at:

http://word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

since way before Word 2003, and maybe before Word 2000 even.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Mark Tangard]

Ahhh THANKS, Doug, that works beautifully.

Is there a list somewhere of what has changed in VBA between 2003 and 2007? Thus
far I'm only discovering these things by accident, and it's been upsetting at
times. (Just finished recoding 90-plus macros that used the FileSearch object;
not fun...)

MT