Dialog Box Question

[MrKremrik]

Hi again,

Does anyone know the code for a - I don't know what it's called - "dialog box" in excel? I need to have a user enter the name of a file, have the code add ".xls" to the end, and then use it to open that file and use it for the address of a vlookup. In a perfect world, I'd love it to spit out an error message if it can't find the file and have the user try again. Thanks in advance!!

 

[AlphaFrog]

Try something like this...

[COLOR=darkblue]Sub[/COLOR] Select_a_File()

    [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook

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

 

[MrKremrik]

Okay it worked, but will I be able to use that file in a vlookup? something like =vlookup($A2, 'wb'!$A$2:$H$1000, 2, false) ? that's the big thing I need it to do.