[VBA] Error 438 when trying to open an existing Workbook

[SDempster]

Hey all.

I am having a weird error. In my function I am opening an existing Workbook, using Find method to locate a search criteria and store its row, and using the row to find a String in "E<ROW>". This works fine on one computer in another code which I used to trouble shoot it, but when I implement the same exact code on my other computer in a different script I am running into "Error 438: Object doesn't support this property or method" right before it attempts to open the Workbook. I looked at both References on both computers and there are no differences besides the bugged computer using 'OLE Automation'. Here's the Function's code. The error happens on the 4th line.

Function findAssEgr(enNum As String) As String 'find the assigned engineer
Dim Log As Workbook
Application.EnableEvents = False
Set Log = Workbooks.Open("<LOCATION>, Test.xlsm", True, True) 'Error occurs right before this line, but doesn't highlight this
Application.EnableEvents = True

Dim rngSearch As Range, rngFound As Range
Set rngSearch = Log.Sheets("EN Log").Range("A:A")
Set rngFound = rngSearch.Find(What:=enNum, LookIn:=xlValues, LookAt:=xlPart) 'Search for the criteria's location in the test log

findAssEgr = Log.Worksheets("EN Log").Cells(rngFound.Row, 5) 'Assigned Engineer's name

Log.Close False

End Function

Another thing to note is that if I exclude the Application.EnableEvents on line 3 I get "Automation Error, Library not registered" at the same location.

 

[RoryA]

Do you have ActiveX controls on worksheets in this workbook?

 

[SDempster]

I'm new to VBA so I'm not positive, but I do know that some other projects on this machine use ADODB and I do have the option to include the MS ActiveX Data Objects 6.1 Library. Or is there another way to find out?

edit: never mind, found out I do have ActiveX controls in Excel.

 

[RoryA]

Does the current workbook work on the other computer?

My suspicion is that you have run into the ActiveX problem caused by the MS updates released in December (see the banner at the top of the forum).

I should mention that the file name here looks weird to me:

Set Log = Workbooks.Open(", Test.xlsm", True, True)

but I wouldn't expect a 438 error from it.

One last thing, do you have any references set in this project to other applications? (eg Word, Outlook)

 

[SDempster]

ActiveX: I'll look into it, but by the looks of it this script isn't using ActiveX controls.

File name: That was a mistake. I changed the name of the file for this post for security reasons. It's inputted correctly in the actual code.

References: Yes, I am also Referenced to Microsoft Outlook 14.0 Data Library as this script deals with emails. The machine which correctly runs the function does not use the Outlook reference (my mistake for saying it did. I overlooked it). But when I enable that reference it still works.

 

[RoryA]

Does the Outlook reference appear above the Excel one in the references list? (very unlikely, but I've seen some strange things!)

If there are activeX controls on a sheet in the workbook anywhere, they can cause this problem, even if they are unrelated to the script in question.

One other thing to check - is there any code in the Open event of the workbook being opened?

 

[SDempster]

Outlook Reference: Yes, it is above the Excel reference.

New Workbook Code: Yes, there is code. It doesn't seem to be a problem on the other machine though.

 

[RoryA]

That's very odd - assuming this code is in Excel, not Outlook?

 

[SDempster]

Nope, I believe it's in Outlook. Then I will add that the working machine was using an Excel VBA and the bugged machine is using Outlook VBA.

 

[RoryA]

Oh. In that case it needs to be completely rewritten:

Function findAssEgr(enNum As String) As String 'find the assigned engineer
Dim appXL As Excel.Application
Dim rngSearch As Excel.Range, rngFound As Excel.Range
Dim Log As Excel.Workbook

Set appXL = CreateObject("Excel.Application")
appXL.EnableEvents = False
Set Log = appXL.Workbooks.Open(", Test.xlsm", True, True) 'Error occurs right before this line, but doesn't highlight this
Application.EnableEvents = True

Set rngSearch = Log.Sheets("EN Log").Range("A:A")
Set rngFound = rngSearch.Find(What:=enNum, LookIn:=xlValues, LookAt:=xlPart) 'Search for the criteria's location in the test log

If Not rngFound Is Nothing Then findAssEgr = Log.Worksheets("EN Log").Cells(rngFound.Row, 5) 'Assigned Engineer's name

Log.Close False
appXL.Quit

End Function

for example.