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 SubThis 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 SubNote 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 SubOpen 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 SubOpen 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 SubIn 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 SubOpen 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 SubI 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)