VBA Macro to Ask User to Browse for Excel File to Open

[ekfasy]

Hello,

I'm trying to write vba code that will ask the user to browse for a certain excel spreadsheet on their computer, then open that file. Is this possible?

I currently have it set up to ask the user to input the file name to open it, but that requires the user to make no errors in their typing...which is not reliable.

Any suggestions would be much appreciated.

Thanks in advance!

 

[seguin85]

I did something similar in the past, I'll show you some of what I used and hopefully you can amend it to fit your needs:

Sub Openit()

Dim YESorNO As String
myPath = ***location of the file****
myFile = Dir(myPath & "*.*",vbNormal)

Do While myFile<> ""
YESorNO = MsgBox("Do you wish to open " & myFile, vbYesNo)

If YESorNO = vbYes Then
   Workbooks.Open myPath & myFile
Exit Sub
ElseIf YESorNO = vbNo Then
Loop

End If

 

[ekfasy]

Seguin85,

Thank you so much! Would this still work if all of the users referred to their own personal documents folder located on their desktop, or would I have to set up a shared folder on the drive and use that location to input into the coding in order to prevent the users from having to change the file location themselves?

 

[Fishboy]

Hello,

I'm trying to write vba code that will ask the user to browse for a certain excel spreadsheet on their computer, then open that file. Is this possible?

I currently have it set up to ask the user to input the file name to open it, but that requires the user to make no errors in their typing...which is not reliable.

Any suggestions would be much appreciated.

Thanks in advance!

I did something similar in the past, I'll show you some of what I used and hopefully you can amend it to fit your needs:

Sub Openit()

Dim YESorNO As String
myPath = ***location of the file****
myFile = Dir(myPath & "*.*",vbNormal)

Do While myFile<> ""
YESorNO = MsgBox("Do you wish to open " & myFile, vbYesNo)

If YESorNO = vbYes Then
   Workbooks.Open myPath & myFile
Exit Sub
ElseIf YESorNO = vbNo Then
Loop

End If

I'm not convinced that will do the trick. If I have understood correctly then try this:

Sub OpenFileDialog()
' Defines variables
Dim TargetFile As String


' Open dialogue box to browse for file
TargetFile = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")


' If no file is selected then...
If TargetFile = False Then
    ' Display message box with an error
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    ' Exit the macro
    Exit Sub
' Else if a valid file is selected then...
Else
    ' Open the selected workbook
    Workbooks.Open FileName:=TargetFile
End If


End Sub

 

[Norie]

You could try using Application.GetOpenFilename.

 

[ekfasy]

Fishboy,

Your coding worked perfectly! Except... when I click on the .xls file that I want to open, an error pops up saying "Type mismatch" and does not open up the file. Any ideas on what the problem is?

Thank you!!!!!

 

[mathematician]

Hello,

I'm trying to write vba code that will ask the user to browse for a certain excel spreadsheet on their computer, then open that file. Is this possible?

.............

application.GetOpenFilename() allows a user to browse for files, select and get the full file name including the path (doesn't open the file). Assign this to a variable of your choice and use workbooks.open to open the file.

Sub browseFileToOpen()
Dim myFile As Variant 'Using string will evaluate to an error
'Restrict browsing other files - Only excel files
myFile = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
If myFile = False Then
'Do nothing
Else
Workbooks.Open (myFile)
End If
End Sub

 

[ekfasy]

Mathematician,

Thank you so much! It works now

And thank you to everyone who also helped and gave input- I really appreciate it!!!

 

[mathematician]

Fishboy,

......................................... "Type mismatch" .................................. Any ideas on what the problem is?

Thank you!!!!!

String is the reason behind that error as this is an array of filenames. Use variant instead of string (Dim TargetFile as Variant)

 

[Norie]

Application.GetOpenFilename retutns an array only if MultiSelect argument is set to True.

The type mismatch in this case can be avoided by replacing False with "False".

If TargetFile = "False" Then