VLookup in VBA to a closed Excel file

[MikeAuz1979]

Hi,

Using Excel 2000 I have a function that simplifies the vlookups in cell formulas (Constructed from various posts in this forum.)

This works great when the file 'Margin Report - Data.xls' is open but not when it's closed, any ideas on how to put in a file location so it can still find the file when it's closed (The error I'm getting when running this with the file closed is 'Subscript out of range')

I tried simply pasting in the folder location but this didn't work. [i.e set ws = Workbooks("C:\FolderName\Margin Report - Data.xls") etc.]

I also tried opening the file at the start of this code but excel appears not to let you open other excel files from a function (Works ok from a sub)

Thanks heaps for any help
Mike

----Code Start---

Function LoadVLookup(SiteNumber As Object, ColumnNo As Long) As Variant
On Error GoTo ErrHandler
Dim ws As Worksheet, rng As Range, Result As Variant

Set ws = Workbooks("Margin Report - Data.xls").Sheets("Margin Report - Forecast Export")
Set rng = ws.Range("B:AC")

Result = Application.VLookup(SiteNumber, rng, ColumnNo, 0)

If Not IsError(Result) Then
LoadVLookup = Result
Else
LoadVLookup = 0
End If

Exit Function
ErrHandler:
MsgBox Err.Description
End Function

----Code End---

 

[SkipVought]

Hi,

You could use a GoSub call. Open and close the workbook in the GoSub routine.

Sub Main()
.....
    GoSub LdVlookup
.....
Exit Sub
LdVlookup:
'open workbook
'lookup code
'closeworkbook
  Return
End Sub

Skip,

_[red][/red]

 

[MikeAuz1979]

Thanks for answering Skip but I'm afraid I don't understand do you mean that the cell should be calling this sub somehow and then the result is returned to the cell? Just to be really specific I have the code = LoadVLookup($H22,X$6) in a cell which is then calling the function in my first post.

I just had another look and now I'm thinking that maybe you mean that I kinda keep the function as it is but do a call to a sub that opens the workbook (To get around the functions not opening a workbook problem) I tried this (and doing this in a gosub as well but it runs through the workbook.open line of code and does nothing.)

Thanks again for the help so far.

(And an overall thanks for the masses of help you've given many people on Tek-Tips, I see you name all over the place and I have a very high regard for the advice you give.)

 

[SkipVought]

I'm saying that the only way to get a value out of the other workbook is to OPEN the other workbook, which you cannot do in a function that you use on a SHEET.

Skip,

_[red][/red]

 

[Gavona]

The workbooks collection contains only open workbooks. So adding the folder location didn't help.

If you
Dim rng as string
set rng= Workbooks("C:\FolderName\Margin Report - Data.xls"!("B:AC")
..then I reckon you would get the result you want.

Your function appears to provide a number of benefits:
1. Prompts for sitenumber and column number may be more helpful than "lookup value" and "Column Index number"
2. avoids need to specify the lookup range
3. Copes when lookup does not find a match or the result of a match is an error value.

You probably know that 3 can be done within a workbook formula - though that formula does become more complex.

You may not know that you can create a named range to refer to a cell in another workbook (Insert,name, Define). So:
=VLOOKUP(A3,MyRng,3,0)
can be the same as:
=VLOOKUP(A3,'C:\Documents and .....\My Documents\[Test Me.xls]Sheet1'!$A:$C,3,0)
once you have defined the named range.


Gavin

 

[Gavona]

oops I meant to suggest
rng= "Workbooks(""C:\FolderName\Margin Report - Data.xls""!(""B:AC"")"

I haven't tested that solution however , and given Skip's post I now wonder...




Gavin