VBA GetOpenFileName

Excel VBA GetOpenFileName

There are many ways to open any file. But there are also multiple ways to open the same file as well. In VBA, we have different functions by which we can open any file. We can open the file by putting the path of the file in VBA code, we can even record this process as well. But the best way is to open any file in VBA is with the help of VBA GetOpenFileName application. As the name says, it is purely made to application on opening any file in VBA.

In other functions or applications which works on the same principle, we may need to copy the file of the file separated by backslashes, keeping folder name and exact spaces between the backslash. Also, we may need to enter the exact file name along with the extension which we want to open. But in VBA GetOpenFileName, we just need to select the file from the location. It will give the box from where we can browse the file where it is kept and the extension of the file will also get fixed.

Syntax of GetOpenFilename in VBA

Below is the argument of VBA GetOpenFileName application.

• FileFilter – In this, we can define what kind of file we want to see. For example, if we mention “Excel Files,*.xlsx” then it will find and show all the excel files of .xlsx format. If we mention “PDF Files,*.pdf” then it will find and show all the pdf files only.
• FilterIndex – By this, we can allow a user to restrict to select the file type. We can specify the number of files that we need to show.
• Title – It shows the file selected file into the dialog box title.
• ButtonText – It is only for MacBook users.
• MultiSelect – If we select multiple files then it will be TRUE or else it will be FALSE which is anyway a default value.

How to Open File Using GetOpenFileName in Excel VBA?

Below are the different examples to open a file using GetOpenFileName application in excel VBA.

Excel VBA GetOpenFileName – Example #1

In this example, we will see how to open an excel file first. Follow the below steps to use GetOpenFileName in Excel VBA:

Step 1: Go to Insert menu and click on Module to open it.

Step 2: Write the subcategory of VBA Getopenfilename or choose any other name as per your choice to define Subcategory.

Code:

Sub OpenFile()

End Sub

Step 3: Now define a variable and give it a String as shown below. Choosing a String because we will be choosing or selecting the file name.

Code:

Sub OpenFile()

Dim A As String

End Sub

Step 4: Now in defined variable A use Application as shown below.

Step 5: Now search and select the GetOpenFileName from the activated list of applications as shown below.

Step 6: Now use MsgBox so that we would get the location of the file which we select.

Code:

Sub OpenFile()

Dim A As String
A = Application.GetOpenFilename()
MsgBox A

End Sub

Step 7: Now compile the code by pressing the F5 function key and run it on clicking on the Play button. Now browse the file which we want to select. Here we have followed the path and selected the excel file named as Test File which usually is in .xlsx format. Click on Open.

It will show the path of that file which we have selected previously into a message box as shown below.

Step 8: Now as per syntax we will use Filefilter into the bracket of GetOpenFilename application. As we know it is meant for choosing the type of file which we want to open it. So we will put “Excel Files” for it as shown below.

Code:

Sub OpenFile()

Dim A As String
A = Application.GetOpenFilename(Filefilter:="Excel Files")
MsgBox A

End Sub

Step 9: Now put the extension of Excel file which we have selected earlier. It was “.xlsx”, so we will keep that too in FileFilter.

Code:

Sub OpenFile()

Dim A As String
A = Application.GetOpenFilename(Filefilter:="Excel Files, *.xlsx")
MsgBox A

End Sub

Step 10: Now, we will run the complete code again. In the browse folder, we will get only Excel files which are available in that folder which we have browsed previously.

And if we try to see any other file format or extension in Excel files drop down then it will again show Excel files only.

Excel VBA GetOpenFileName – Example #2

In the same manner, we can see open the file name with extension “.pdf”. Follow the below steps to use GetOpenFileName in Excel VBA:

Step 1: Now again write the code which we have seen in example-1.

Code:

Sub OpenFile1()

Dim A As String
A = Application.GetOpenFilename()
MsgBox A

End Sub

Step 2: It will take us to the same window or else browse to that same folder. Now select any of PDF files and click on Open.

It will give us the path of the selected file.

Step 3: Now to get the selected PDF file, in FileFilter syntax, write “PDF Files” instead of “Excel Files” and select the extension of PDF which is “.pdf” as shown below.

Code:

Sub OpenFile1()

Dim A As String
A = Application.GetOpenFilename(Filefilter:="PDF Files, *.pdf")
MsgBox A

End Sub

Step 4: Again compile the code and run after it. We will get the browser folder to open the files. In that, we will only get the files with extension “.pdf” as shown below.

And if try to filter other file formats, then it will again allow us to see only PDF Files only as shown below.

And if we don’t select any file and exit from the window then it will give a default message which is False.

Pros of Excel VBA GetOpenFileName

• VBA GetOpenFileName helps us to directly open the file without pasting the path of that file in VBA code.
• We don’t need to change the path of the files multiple times.
• Browsing and selecting the files through this is as simple as going to that folder to open it.

Cons of Excel VBA GetOpenFileName

• We need to change the extension of a file which we want to open if there is a change in that.

Things to Remember

• Change the extension of the file whenever you need to open a different version of the file.
• Message Box with a message as FALSE is the default message which will come every time when we don’t select any file to open.
• Such kind of process cannot be created with the help of the Record Macro function.
• Remember to save the file as a Macro Enabled Excel format. So that written VBA code will be preserved.

Recommended Articles

This is a guide to VBA GetOpenFileName. Here we discuss how to open a file using GetOpenFileName application in Excel using VBA code along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Find and Replace
2. Group Worksheets In Excel
3. VBA Workbook
4. Cell References in Excel