VBA code to browse and open an Excel file

Am trying to automate, browse the files through search button and open the files and it should be pasted in the next sheet of my macro sheet. Pleae help to do in excel vba.

I try to open two files and i need to compare the files.

asked May 1, 2018 at 13:45

2

This code will let you browse to a selected file and then open it

Sub OpenFile()
Dim strFileToOpen As Variant

strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

If strFileToOpen = False Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
Else
    Workbooks.Open Filename:=strFileToOpen
End If

End Sub

This will get you started. Once it is open, you can write code to copy the sheet(s) into your macro file.

ZygD

20.2k39 gold badges75 silver badges93 bronze badges

answered May 1, 2018 at 14:02

CFOCFO

2881 silver badge10 bronze badges

With VBA in Excel, you can easily open one or more Excel files by specifying their location.

This is made possible by the Workbooks.Open method, which takes the file location as the argument and opens that Excel file.

You can do a lot more with the Workbooks.Open method, such as opening all the files in a given folder, opening files as read-only, opening files and then saving them with a different name, etc.

In this tutorial, I will show you how to use VBA to open Excel files and all the amazing things you can do with it.

• Workbooks.Open Method
• Open Workbook By Specifying the File Path
  • Open Multiple Excel Files Together
• Open All Excel Workbooks in a Folder
  • Open Excel Files Based on the Name
• Opening a Password Protected Excel Workbook
• Open Excel File By Showing the Open Dialog Box

Workbooks.Open Method

In Excel VBA, you can use Workbooks.Open method to open an Excel file, where you need to specify the file path of the Excel workbook that you want to open.

Below is the syntax of the Workbooks.Open method

expression.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

While the Workbooks.Open method can use 15 arguments, in most cases, we only need to use two or three of them.

I will show you examples that will make it clearer how to use these arguments.

Open Workbook By Specifying the File Path

Let’s first look at how to open an Excel file where you know the entire file path.

Below is the VBA code that would open an Excel file named Example.xlsx in the folder called Project on my system:

Sub OpenWorkbook()
Workbooks.Open ("C:\Users\sumit\OneDrive\Desktop\Project\Example.xlsx")
End Sub

Note that the part of the Excel file needs to be in double quotes, and you need to specify the entire file path including the name of the file and the extension.

In case Excel is not able to find the file at the location that you specified, it will show you an error as shown below.

Open Multiple Excel Files Together

If you want to open multiple Excel files using one single subroutine, you can do that as well (by using multiple Workbooks.Open methods with the file paths)

Below I have a VBA code that would open three Excel files, where I need to specify the path for each file in separate Workbooks.Open method.

Sub OpenMulitpleWorkbook()
Workbooks.Open ("C:\Users\sumit\OneDrive\Desktop\Project\Example1.xlsx")
Workbooks.Open ("C:\Users\sumit\OneDrive\Desktop\Project\Example2.xlsx")
Workbooks.Open ("C:\Users\sumit\OneDrive\Desktop\Project\Example3.xlsx")
End Sub

Open All Excel Workbooks in a Folder

If you have multiple Excel files in a folder, and you want to open all the files in that folder in one go, you can do that easily using the Workbooks.Open method along with the simple Do While loop.

Below I have code where I have specified the folder path, and when this code is executed, it goes through each Excel file in the folder and opens it.

Sub OpenFilesFromFolder()
    Dim wb As Workbook
    Dim FolderPath As String
    Dim FilePath As String
        FolderPath = "C:\Users\sumit\OneDrive\Desktop\Project\"
        FilePath = Dir(FolderPath & "*.xls*")
        Do While FilePath <> ""
            Set wb = Workbooks.Open(FolderPath & FilePath)
            FilePath = Dir
        Loop
End Sub

Open Excel Files Based on the Name

If you only want to open specific Excel files in a folder based on its name, you can modify the above VBA code to do that.

Below I have a VBA code where it would open only those Excel files in the specified folders that have the word “Sales” in the name of the Excel file.

Sub OpenFilesFromFolder()
    Dim wb As Workbook
    Dim FolderPath As String
    Dim FilePath As String
        FolderPath = "C:\Users\sumit\OneDrive\Desktop\Project\"
        FilePath = Dir(FolderPath & "*Sales*" & ".xls*")
        Do While FilePath <> ""
            Set wb = Workbooks.Open(FolderPath & FilePath)
            FilePath = Dir
        Loop
End Sub

In the above code, I have used Dir(FolderPath & “*Sales*” & “.xls*”) as the FilePath, where the word Sales is wrapped in asterisks (*).

This ensures that only the files with the word ‘Sales’ in the name would get opened, and the rest would be ignored.

Opening a Password Protected Excel Workbook

You can also open password-protected workbooks by specifying the password in the VBA code.

Below I have a VBA code that would open a password-protected Excel file, where the password is Excel123

Sub OpenExcelFile()
Workbooks.Open "C:\Users\sumit\OneDrive\Desktop\Project\Example.xlsx", , , Password:="Excel123"
End Sub

Open Excel File By Showing the Open Dialog Box

In the examples covered so far, I have specified the file name or the path of the files that need to be opened in the VBA code itself.

However, you can use VBA to open the Open File dialog box, where the user can navigate to the file and select it to open it.

Below is the VBA code that would show the Open dialog box, where the user can select the file and open it.

Sub OpenFileDialogBox()
On Error Resume Next
    Dim FilePath As String
    FilePath = Application.GetOpenFilename()
    Workbooks.Open (FilePath)
End Sub

I have used On Error Resume Next in case the user decides to close the Open dialog box without selecting any file. If this statement is not used, it will show an error.

In this short tutorial, I showed you a couple of different ways you can use to open workbooks using VBA in Excel.

While you may not use these codes to simply open an Excel file, the concept could be quite useful when working on bigger VBA projects where you need to open Excel files as part of the code

Other Excel tutorials you may also like:

• Save As Shortcuts in Excel (Quick and Easy)
• How to Automatically Open Specific Excel Files on Startup
• Microsoft Excel Won’t Open – How to Fix it! (6 Possible Solutions)
• Using Workbook Object in Excel VBA (Open, Close, Save, Set)
• Working with Cells and Ranges in Excel VBA (Select, Copy, Move, Edit)
• Working with Worksheets using Excel VBA (Explained with Examples)

How do I browse and open a file in VBA?

How do I create a Browse button in Excel VBA?

How do I browse an Excel file?