Trying to open and excel file - error 91

[prs16001]

I'm trying to open and excel file from word to put text from word into the
excel file. I cannot even get the excel file to open using the code from
http://word.mvps.org/faqs/interdev/ControlXLFromWord.htm

I have my code practically exact. At this point, I'm just trying to open
the file and then close it, so the code is stripped down and does nothing
with the file. I can't even open the file and get the error 91. I couldn't
find what I'm doing wrong on the site. Initially, I want to count the number
of rows in the excel file and in the past have been using
ActiveCell.CurrentRegion.Select
rcount = selection.rows.count

Here is the stripped down code.
sub get_excel_file()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
Dim excel_file As String
Dim excel_file_path As String
excel_file_path = "C:\Documents and Settings\"
excel_file = excel_file_path & "test.xls"

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

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

On Error GoTo Err_Handler

oWB = oXL.Workbooks.Open(FileName:=excel_file)


If ExcelWasNotRunning Then
oXL.Quit
End If

Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing


Err_Handler:

MsgBox excel_file & " caused a problem. " & Err.Description, vbCritical,
"Error:" & Err.Number

If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

 

[Helmut Weber]

Hi,

oWB = oXL.Workbooks.Open(FileName:=excel_file)

try:

set oWB = oXL.Workbooks.Open(FileName:=excel_file)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[prs16001]

Hi,


try:

set oWB = oXL.Workbooks.Open(FileName:=excel_file)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

OK, I changed that line and now get a window with Error:0 containing only
the error message from the message box, no desciption and after that is
OK'ed, a window with Error:91. This is very frustrating. I have a working
macro that opens and gets data from an excel file but that code isn't working
in this macro (and the other macro doesn't have the error handling or the
early binding). It is just one line excel_file = getobject("excel_file.xls").

What am I doing wrong???

 

[Ed]

A couple of suggestions from one who has also spent much time scratching the
head:
(1) Check for open instances of Excel and open temps of your Excel file.
Your GoTo Err-Handler bypasses destroying the objects, and they may still be
open and in use, causing errors.
(2) See >> lines in your code below.

sub get_excel_file()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
Dim excel_file As String
Dim excel_file_path As String
excel_file_path = "C:\Documents and Settings\"
excel_file = excel_file_path & "test.xls"

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err ThenExcelWasNotRunning = True
Set oXL = New Excel.Application
oXL.Visible = True
End If

On Error GoTo Err_Handler

oWB = oXL.Workbooks.Open(FileName:=excel_file)


If ExcelWasNotRunning Then
oXL.Quit
End If

Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

Err_Handler:

MsgBox excel_file & " caused a problem. " & Err.Description, vbCritical,
"Error:" & Err.Number

If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub


HTH
Ed

 

[prs16001]

A couple of suggestions from one who has also spent much time scratching the
head:
(1) Check for open instances of Excel and open temps of your Excel file.
Your GoTo Err-Handler bypasses destroying the objects, and they may still be
open and in use, causing errors.
(2) See >> lines in your code below.

sub get_excel_file()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
Dim excel_file As String
Dim excel_file_path As String
excel_file_path = "C:\Documents and Settings\"
excel_file = excel_file_path & "test.xls"


On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

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

On Error GoTo Err_Handler

oWB = oXL.Workbooks.Open(FileName:=excel_file)


If ExcelWasNotRunning Then
oXL.Quit
End If

Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing


Err_Handler:

MsgBox excel_file & " caused a problem. " & Err.Description, vbCritical,
"Error:" & Err.Number

If ExcelWasNotRunning Then
oXL.Quit
End If


End Sub


HTH
Ed

Thanks, I think not having the exit sub was causing part of the problem.
I'm now getting the 91 error when I put in some excel commands, so just need
to figure it out.