workbooks.open fails with no error

[steve99g]

running excel 2010 on windows 7. trying to open Book2.xlsx from Book1.xlsm. this hard-coded macro works:

Please Login or Register  to view this content.

This function doesn't (no error on the workbooks.open line, but wb throws object variable or with block variable not set. Passing "c:\users\steve\Book2.xlsx" as FileToOpen param

Please Login or Register  to view this content.

[JBeaucaire]

How are you using this function, exactly?

[steve99g]

function will be used to open different workbooks based on user input. parameter FileToOpen will be built elsewhere and passed in. Other UDFs wil act upon contents of opened workbook.

[Norie]

Which line in the code errors?

What happens if you remove On Error Goto ErrTrap?

[steve99g]

the line after set wb = workbooks.open, where I debug.print wb.name - I get the object variable or with block variable #91. I'll try w/o the on error, but usually like to handle errors in the code.

[Norie]

The On Error could actually be hiding an error.

By the way, you've not said where/how you are using this code.

Are you calling it from another sub/function?

[steve99g]

right now, I'm calling the funtion from a cell in Book1.xlsm, =OpenWorkbook1(B2), where cell B2 contains the string "f:\Book2.xlsx". Ultimately, I want the code in an add-in to be able to use in multiple spreadsheets. I took the on error out, and no runtime error is generated (unless I reference a property of wb, which was dim'ed as a workbook.

[Norie]

That's the problem - calling the function from a cell.

User defined functions on worksheets return values to the cell they are in, they don't carry
out other operations like opening files.

[steve99g]

the function does return a string (value indicates if success or failure to open the workbook). can a sub procedure carry out other operations, ie - opening workbooks?

[Norie]

The problem is that you are using the function on a worksheet.

If you called this function from a sub or other function then there'd be no problem.

For example if I run this code I get a message box with the path and filename, and the file is opened.

Please Login or Register  to view this content.