Select file, assign to variable, use in vlookup

[MrKremrik]

Hi I've been on another thread where I asked if it was possible for a user to run a macro that asks them to select a file on the hd to open. I was given the below code. It works well for selecting/opening another file, but what I would really love for it to do would be to assign the file name (just filename.xls) to a variable. From there, I need to use said variable in a vlookup to search between two files. How would this be made possible?

Thanks!!

Sub Select_a_File() Dim wb As Workbook ' Prompt user to select a file With Application.FileDialog(msoFileDialogOpen) .InitialFileName = ThisWorkbook.Path ' Default path .FilterIndex = 3 .Title = "Please Select a File" .ButtonName = "Open" .AllowMultiSelect = False .Show If .SelectedItems.Count = 0 Then Exit Sub ' User clicked cancel Set wb = Workbooks.Open(Filename:=.SelectedItems(1)) End With End Sub</PRE>

 

[Andrew Poulsom]

You can assign wb.Name to your variable.

 

[MrKremrik]

So something like filename = wb.name?

 

[Andrew Poulsom]

Yes.

 

[MrKremrik]

Alrighty, I tried to use that variable in a vlookup and it didn't work. Using the code I posted earlier in conjuction with this =vlookup($a2, 'filename'!$A$2:$Z$2000,9,false) gave me like a weird loop that kept asking me to select the file. What did I do wrong?

 

[Andrew Poulsom]

Try eg:

ActiveCell.Formula = "=vlookup($a2,'[" & filename & "]Sheet1'!$A$2:$Z$2000,9,false)"

 

[MrKremrik]

It's still doing the same thing... It's like it's asking for the file for every line or something. I don't know. Here's the code I'm using. It's just an example code I'm using to try to compare two almost identical worksheets, so the only differences in them are the the two columns I'm vlookup-ing.

Sub Select_a_File()     Dim wb As Workbook
    Dim filename As String
    
        ' Prompt user to select a file
        With Application.FileDialog(msoFileDialogOpen)
        
            .InitialFileName = ThisWorkbook.Path              ' Default path
            .FilterIndex = 3
            .Title = "Please Select a File"
            .ButtonName = "Open"
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
            
            Set wb = Workbooks.Open(filename:=.SelectedItems(1))
            
        End With
    
    filename = wb.Name
    
    
    Range("K2").Select
    ActiveCell.Formula = "=vlookup($a2,'[" & filename & "]Sheet1'!$A$2:$Z$2000,9,false)"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2", "k" & 700), Type:=xlFillDefault
    Range("K2", "K" & 700).Select
    Range("L2").Select
    ActiveCell.Formula = "=vlookup($a2,'[" & filename & "]Sheet1'!$A$2:$Z$2000,10,false)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2", "l" & 700), Type:=xlFillDefault
    Range("L2", "L" & 700).Select
    Range("J1").Select
    
    
End Sub

 

[MrKremrik]

So is there anything you'd recommend to eliminate this error? In a perfect world, I'd only have to select the file once :P

 

[Andrew Poulsom]

Does the workbook being opened have a worksheet named Sheet1? Your code works for me, except that it puts the formulas in the workbook that's opened rather than the workbook that contains the code. But maybe that's what you want.