Vba To Pop Up Window To Choose The File...

[jmazorra]

Hello folks:

The code snippet below is to import a sheet into my main template. Right now, the code looks for the file in the same directory as the template, if the file is not there, a message box pops up advising the user when OK is pressed (see red lines), the workbook closes. However, in order to make my program more user friendly, I would like to add some more functionality to the code, so I would like to have a windows file browser window pop up where the user can browse for the file and select it in whatever directory it is.

Can this window be created in VBA?

Sub ImportFile()

Dim sourceFile As String
Dim firstDestFile As String

sourceFile = "Client Data Dashboard Template.xlsm" 'This is the Template were information will be exported to
firstDestFile = Workbooks(sourceFile).Path & "\Active PEO Clients.xlsm" 'First workbook to open


If Len(Dir(firstDestFile)) = 0 Then 'Check to see if Active PEO Clients.xlsm exists

MsgBox firstDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."

ThisWorkbook.Close savechanges:=False 'Close the Template without changes

Else

With Workbooks.Open(firstDestFile)

If Len(Dir(firstDestFile)) > 0 Then 'If the export file does exist then

.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("Active PEO Clients").Range("A1")

.Close savechanges:=False 'Close the Import file without changes

End If

End Sub

 

[JoeMo]

The GetOpenFilename method will open the windows explorer to allow the user to browse for and select a file or files.

Application.GetOpenFilename

is the syntax.

example:
Dim myFile as string
myFile = Application.GetOpenFilename
Workbooks.Open filename:= myFile

myFile will contain the full path to the file the user selected.

 

[jmazorra]

Thank you,

That was extremely helpful