VBA How to open a file from Input from user via "browse"

[double trouble]

Hello,
I am new to VBA and have a question for more experienced people. I have created a macro in VBA to open anohter excel file which is raw data in a not so pretty format, read certain data to an array, close the file, and put that data into a "pretty" formated sheet. Right now I have hard coded the file extension but would like to allow the user to select via a "browse" file screen which file to open. Is this possible?

I used the following command to open the file
Workbooks.Open "M:\SC Models\Short Circuit Study\Summer Bonin-Luke.xlsx" 'open summer case

and to close the file I use:

Workbooks("Summer Bonin-Luke.xlsx").Close SaveChanges:=False 'close workbook

I have looked in VBA help and in a couple of excel VBA books. I can find how to create dialog boxes and get user input, but cannot find if you can allow the user to browse through windows to select which file to open. Any light you can shine on this would be greatly appreciated.

Thank you.

 

[VoG]

Try like this

Sub GetFile()
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
End Sub

 

[JackDanIce]

Welcome to the board.

This is some code I stumbled upon somewhere (can't remember where) but kept as thought it might come in handy. Think it will help with your requirement and it has helpful remarks to explain what it's doing:

Sub SelectFiles()
    'Declare a variable as a FileDialog object
    'and create a FileDialog object as a File Picker dialog box
    Dim iFileSelect As FileDialog
    Set iFileSelect = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path of each selected item
    'Even though the path is a String, the variable must be a Variant
    'Because For Each...Next routines only work with Variants and Objects
    Dim vrtSelectedItem As Variant

    
        'Use the Show method to display the File Picker dialog box
        'The user pressed the action button
        If iFileSelect.Show = -1 Then
        
            
            For Each vrtSelectedItem In iFileSelect.SelectedItems
                'vrtSelectedItem contains  path of each selected item
                'You can use any file I/O functions you want on the path
                'This example displays file path
                MsgBox "The path is: " & vrtSelectedItem
            Next vrtSelectedItem
            
        End If
        

    'Set object variable to Nothing
    Set iFileSelect = Nothing
End Sub

 

[Comfy]

 Function GetFilenameFromPath(ByVal strPath As String) As String
    
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function
Function isOpen(ByVal strPath As String)
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks(strPath)
        If wBook Is Nothing Then 'Not open
            Application.Workbooks.Open (strPath)
        End If
End Function
Sub FileBrowser()
Dim strPath As String
Dim wb As Workbook
strPath = Application.GetOpenFilename(, , "Select your File")
    If strPath = "" Then Exit Sub
    isOpen (GetFilenameFromPath(strPath))
    Set wb = Application.Workbooks(GetFilenameFromPath(strPath))
End Sub

 

[double trouble]

Thank you for that. It opens a screen for browsing perfectly exactly like I wanted. One follow up question - how do I close that file after I get the data I need out of it? Before I was hard coding which file to close, now the whole file extension is in a variable. How do I extract the file name to be able to close the open file, or is there an easier way to close the last file opened?

Sorry, I started typing this before the comments from JackDanIce and Comfy. I am looking into their codes and will respond soon.

 

[VoG]

Try like this

Sub GetFile()
Dim fNameAndPath As Variant, wb As Workbook
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Set wb = Workbooks.Open(fNameAndPath)
'
'do stuff
'
wb.Close savechanges:=True 'or false
End Sub

 

[double trouble]

VoG,
Thank you, it works great. I am looking into the other two responses to figure out what their code does.

 

[double trouble]

Thank you all for the responses. I have read all the codes you posted and it seems like Vog's code will do what I need the best. Thank you again for your responses.