Runtime 91 Program won't run w/Excel open

[Anonymous]

I'm having a problem getting my code to work properly when the excel workbook that is being referenced is already open. It works fine when it is closed prior to executing the program, but when it is already open, I get a error 91.

I have a little bit of code in there that is supposed to cycle through the open XL workbooks and pick the right one based on the workbook that is passed into this routine. That isn't the problem. The problem occurs when It first sets a variable = to one of the variables in excel.

I tried to get the program to "activate" the exact worksheet that I was trying to pull data from. So below you'll see "fme = activeworkbook.name. This is where the error occurs. If I comment that out, then I get an error the next time something from excel is referenced....

I must be declaring something wrong. If anyone has any ideas, please let me know! I've searched and searched and have wound up empty handed...

Thanks!!!

Here is the routine where the code is giving me errors:

Function executeprogram_test(ACAD_folder, XL_file)
'**********************
'declare excel objects

Dim excelApp As Excel.Application
'Following objects moved below to prevent early binding
'Dim excelApp As Object
'Dim wbkobj As Object
'Dim shtobj As Object

'**********************
'set excel objects

Dim blnXLRunning As Boolean
blnXLRunning = IsExcelRunning() 'see routine below
If blnXLRunning Then
Set excelApp = GetObject(, "Excel.Application")
Else
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.UserControl = True
End If

Dim wbkobj As Workbook
Dim wbkobjs As Workbooks
Dim shtobj As Worksheet
Dim shtobjs As Worksheets

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Dim CheckOpen As Boolean 'set to true if desired workbook is open
Dim OpenCnt As Long
Dim XL_file_Name As String
For OpenCnt = 1 To excelApp.Workbooks.Count
If excelApp.Workbooks(OpenCnt).FullName = XL_file Then
CheckOpen = True
XL_file_Name = excelApp.Workbooks(OpenCnt).Name
Else
CheckOpen = False
End If
Next

'On Error GoTo ErrHand
If CheckOpen Then 'if open, don't re-open
Set wbkobj = excelApp.Workbooks(XL_file_Name)
Set shtobj = wbkobj.Worksheets(1)
'Dim blahobj As Object
'Set blahobj = excelApp.Workbooks(XL_file_Name).Worksheets(1)
'wbkobj.Activate
'shtobj.Activate

'****************** Error occurs here **********************
Dim fme As String
fme = ActiveWorkbook.Name
'MsgBox (ActiveWorkbook.Name)
'********** If excel is closed, it goes here and no error occurs
Else
Set wbkobj = excelApp.Workbooks.Open(XL_file)
Set shtobj = excelApp.Worksheets(1)
End If

'************************
'Do stuff here

' Define initial variables
Dim Bottom_loc As Long
'MsgBox (Err.Number)
Bottom_loc = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
' Range("A65536").End(xlUp).Row
Dim farright_Loc As Integer
farright_Loc = Range("A1").End(xlToRight).Column

Dim Search_Criteria As Variant
Set Search_Criteria = Worksheets(1).Range(Cells(2, 1), _
Cells(Bottom_loc, 1))

Dim SC_Val2() As String
Dim SCidx As Long
SCidx = 1
For Each cell In Search_Criteria
'MsgBox (cell.Value)
ReDim Preserve SC_Val2(1 To SCidx)
SC_Val2(SCidx) = cell.Value
SCidx = SCidx + 1
Next

Dim dumbierange As Variant
Set dumbierange = Worksheets(1).Range(Cells(2, 2), Cells(Bottom_loc, 2))
Dim Teet As Long
For Teet = 1 To UBound(SC_Val2)
dumbierange(Teet).Value = SC_Val2(Teet)
Next

'************************
'Terminate Excel objects here

Set excelApp = Nothing
'Dim excelApp As Object
Set wbkobj = Nothing
Set wbkobjs = Nothing
Set shtobj = Nothing
Set shtobjs = Nothing

End Function


'/////////////////For Making sure program runs open or closed///////////////
' Thank Randall Rath from AutoDesk Discussion groups.
' http://discussion.autodesk.com/thread.jspa?messageID=415254
Function IsExcelRunning() As Boolean
Dim excelApp As Excel.Application
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set excelApp = Nothing
Err.Clear
End Function

[Anonymous]

I replaced
fme = ActiveWorkbook.Name

with
fme = excelapp.ActiveWorkbook.Name

and that works, but now I get an error when it tries to set that range:
Bottom_loc = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row

I tried putting excelapp...., excelapp.wbkobj....., excelapp.shtobj........, excelapp.activeworkbook....., worksheets(1)....... and maybe a few more with no luck. Does anyone have any ideas?

Thanks again.