open Excel from Word 2000

[Al UK]

I am sure I am probably missing something silly, but I
have copied code from the MVP site to open an instance of
excel where one is not already open. Unfortunatley I get
a "user type not defined" error when I try to run it.
What have I missed? (best point it out simply unless I
also missed it from the MVP site- it mentions adding a
reference, but not sure if th code is doing that or i was
supposed to have done something?)

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
'Dim oSheet As Excel.Worksheet
'Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\CONTRACT\CONTRACTnew.xls"

'If Excel is running, get a handle on it; otherwise start
a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line:
oXL.Visible = True here; but your code will run faster if
you don't make it visible

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
'For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here
'get next sheet
'Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
'Set oRng = Nothing
'Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " &
Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

 

[Andi Mayer]

'If Excel is running, get a handle on it; otherwise start
a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

have you missed the ['] before a

or is my newsreader breaking the lines on the wrong side?

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Al Uk]

lol, its your reader.
I think I have found the answer- the reference for MS
Excel Object Library 9 (in my case) requirs a tick- but it
doesn't appear until after you have tried to run the
code.. ro so it seems. Had the same on 2 pcs now.

-----Original Message-----

'If Excel is running, get a handle on it; otherwise start
a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

have you missed the ['] before a

or is my newsreader breaking the lines on the wrong side?

If you expect an answer to a personal mail, add the

word "manfred" to the first 10 lines in the message