Expert: Chad Welch - 2/19/2008

Question
Hi,  I am really new to VBA.  
I want to create a program in Excel where a form asks a user for a file name, or lets them browse for the file like file>>open does in any application and once the file has been selected opens it and rips data from a specified cell range.  I can make the user form and I am confident I can create the code to copy the data from the cell range.  However I have no idea how to code the file location/ browse   to file part.  Can you help please?

Answer
Dave,
The GetOpenFilename method will do exactly what you need.  It uses the open file dialog box to let the user browse to a file and select it.  The short snippet of code below gives an example. There are several options that can be used to set filters and other options.  Search for it in the VBA help to find all of the settings that can be used.  If you would like to set a specific folder to have the open dialog start in, use the ChDir method (also shown in the code below). To open the file after the user has selected it, use the Open method.

Best regards,
Chad Welch

Sub GetFile()
   Dim txtFName As String
   Dim wbkOpened As Workbook
   ChDir "C:\Users\"
   txtFName = Application.GetOpenFilename
   Set wbkOpened = Workbooks.Open(txtFName)
End Sub