Workbooks.Open only returns current file?

[johnnygeo]

I have a custom worksheet function which will perform a vlookup from an external Excel file. The specific external file will depend on the parameters passed to the function.

The simplified code below works great as long as the external file is open.

It does not work if the external file is not already open. From watching the locals window, I can see that the myBook object gets set to the *current* file. What would cause the workbooks.open(myFile) to return the current file instead of the correct external file?

Function JGLookup(myFile As String, myLookup As String)

    On Error GoTo errorLine
    
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim myRange As Range
    
    myPath = "C:\lookup\"
    Set myBook = Workbooks.Open(myFile & myPath, 0, True) '<---- This is where I have the problem
    Set mySheet = myBook.Sheets(1)
    Set myRange = mySheet.Range("$A$2:$P$10000")
    
    myAnswer = Application.WorksheetFunction.VLookup(myLookup, myRange, 9, False)
    JGLookup = myAnswer
    Set myBook = Nothing
    
errorLine:
    laborStat = Error$
    Set myBook = Nothing
    
End Function

 

[PHV]

Typo ?
Open(myPath & myFile,

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zathras]

Not sure, but have you tried
[tt]
myPath & myFile
[/tt]
instead of the other way around?

 

[johnnygeo]

oops. You are correct, there was a typo in the code as posted. There were a couple of others, so I am reposting the code. However, the behavior is the same as I described above: works if the external file is open, otherwise it sets myBook to the current file.

Function JGLookup(myFile As String, myLookup As String)

    Stop
    On Error GoTo errorLine
    
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim myRange As Range
    
    myPath = "C:\lookup\"
    Set myBook = Workbooks.Open(myPath & myFile, 0, True) '<---- This is where I have the problem
    Set mySheet = myBook.Sheets(1)
    Set myRange = mySheet.Range("$A$2:$P$10000")
    
    myAnswer = Application.WorksheetFunction.VLookup(myLookup, myRange, 9, False)
    JGLookup = myAnswer
    Set myBook = Nothing
    Exit Function
    
errorLine:
    JGLookup = Error$
    Set myBook = Nothing
    
End Function

 

[PHV]

it sets myBook to the current file
Hopefully the current file has a different name than the external file ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnnygeo]

yes, the current file in my test case is "c:\Labor Management Tool v2.xls", and the external file is "LookupTable1.xls" in the same directory.

 

[PHV]

in the same directory
So what is the purpose of myPath = "C:\lookup\" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Can you show your code that calls JGLookup in the instance when the problem occurs?

Regards,
Mike

 

[johnnygeo]

Wow, I'm making a real dog's breakfast of this post.

PHV:
Main file is c:\lookup\Labor Management Tool v2.xls
Lookup file is c:\lookup\LookupTable1.xls

Mike:
a cell in the main file contains the formula:

=JGLookup("LookupTable1.xls",C2&"-"&A11)

 

[xlbo]

When I try it, it actually sets myBook to Nothing - which is what I would expect 'cos I didn't think you could open a workbook via a worksheet function. Once a function is used in a worksheet, it cannot alter any cell other than the one it resides in - and that includes doing any operations on cells / workbooks outside the current file as well.

Still - willing to be proved wrong (again)

Rgds, Geoff

"Errors have been made....DPlank will be blamed"

Please read FAQ222-2244 before you ask a question

 

[johnnygeo]

On a whim, I modified the code slightly to open the new workbook in a new instance of Excel. This seems to work, but I can't imagine why. Perhaps it has something to do with the limitation Geoff mentions. Thanks everyone for your help.

revised code:

Function JGLookup(myFile As String, myLookup As String)

    Stop
    On Error GoTo errorLine
    
    Dim xlApp As New Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim myRange As Range
    
    myPath = "C:\lookup\"
    Set myBook = xlApp.Workbooks.Open(myPath & myFile, 0, True) '<---- This is where I have the problem
    myBookName = myBook.Name
    Set mySheet = myBook.Sheets(1)
    Set myRange = mySheet.Range("$A$2:$P$10000")
    
    myAnswer = Application.WorksheetFunction.VLookup(myLookup, myRange, 9, False)
    JGLookup = myAnswer

exitLine:
    myBook.Close
    Set myBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function
    
errorLine:
    JGLookup = Error$
    GoTo exitLine
    
End Function

 

[johnnygeo]

of course, I have yet another typo. myAnswer line should read:

myAnswer = xlApp.WorksheetFunction.VLookup(myLookup, myRange, 9, False) 'note new application instance