EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA File & Directories Functions VBA GetOpenFileName
Secondary Sidebar
VBA File & Directories Functions
  • VBA File & Directories
    • VBA Get Cell Value
    • VBA FileDialog
    • VBA Delete File
    • VBA Check File Exists
    • VBA DIR
    • VBA GetOpenFileName
    • VBA Write Text File
    • FileCopy in VBA
    • VBA FreeFile
    • VBA Solver

VBA GetOpenFileName

By Ashwani JaiswalAshwani Jaiswal

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

Let us look below the formula for GetOpenFilename in Excel VBA.

Syntax of VBA GetOpenFileName

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

You can download this VBA GetOpenFileName Excel Template here – VBA GetOpenFileName Excel Template

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:

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,389 ratings)

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

VBA GetOpenFileName Example 1-1

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

VBA GetOpenFileName Example 1-2

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

VBA GetOpenFileName Example 1-3

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

VBA GetOpenFileName Example 1-4

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

VBA GetOpenFileName Example 1-5

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

Use MsgBox Example 1-6

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.

VBA GetOpenFileName Example 1-7

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

VBA GetOpenFileName Example 1-8

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

VBA GetOpenFileName Example 1-9

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

VBA GetOpenFileName Example 1-10

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.

VBA GetOpenFileName Example 1-11

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 files Example 1-12

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

VBA GetOpenFileName Example 2-1

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.

Select PDF files Example 2-2

It will give us the path of the selected file.

Path of selected file Example 2-3

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

VBA GetOpenFileName Example 2-4

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.

VBA GetOpenFileName Example 2-5

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

PDF Files Example 2-6

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

Default Message

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. VBA Get Cell Value
  3. VBA Workbook
  4. VBA RGB
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more