Expert: Jan Karel Pieterse - 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
Something like this will ask for a filename (the True argument in GetOpenFilename enables the user to select more than one file):

Sub GetOpenFileNameExample3()
   Dim lCount As Long
   Dim vFilename As Variant
   Dim sPath As String
   Dim lFilecount As Long
   sPath = "c:\windows\temp\"
   ChDrive sPath
   ChDir sPath
   vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to open", , True)
   If TypeName(vFilename) = "Boolean" Then Exit Sub
   For lCount = 1 To UBound(vFilename)
       Workbooks.Open vFilename(lCount)
   Next
End Sub