How to Launch VBA Macro Editor in Excel
Steps:
- Open the Developer tab.
- Select the Visual Basic command.
- The Visual Basic window will open.
- From the Insert option, we will choose the new Module to write the VBA code.
Excel VBA to Open Workbook from Path in Cell: Do It with a Simple Code
It is simple to open a workbook in Excel. We can open the workbook from a cell value using the directory path. We will show how to open a workbook from the path in a cell using Excel VBA. The steps to do the task are mentioned below.
- We put the file name in cell B5 and the file path in cell C5.
- We click the Developer tab and open the Visual Basic editor.
- Run the below-mentioned code in a module.
- It will open the “New Workbook” file.
Sub OpenWorkbookFromCell()
Dim FilePath As String
Dim wb As Workbook
' Get the file path from the cell
FilePath = Range("C5").Value
' Check if the file exists
If Dir(FilePath) <> "" Then
' Open the workbook
Set wb = Workbooks.Open(FilePath)
Else
MsgBox "File not found!"
End If
End SubVBA Breakdown
Sub OpenWorkbookFromCell()- First, we create a subprocedure named “OpenWorkbookFromCell”.
Dim FilePath As String
Dim wb As WorkbookThen, we declare two variables named “FilePath” and “wb” as strings.
FilePath = Range("C5").ValueIt changes the “FilePath” variable’s value to that of cell C5. This presupposes that cell C5 of the current worksheet holds the file location.
If Dir(FilePath) <> "" ThenThe Dir function determines whether the file indicated by “FilePath” exists. If the file is present, the code inside the If block will run. If not, the Otherwise block’s code will be carried out.
Set wb = Workbooks.Open(FilePath)Use the Workbooks. Open method to open the file from the “FilePath”.
Else
MsgBox "File not found!"If the file is not available, then the MsgBox will show the above message.
Some Alternate Ways of Using Excel VBA to Open Workbook from Path in Cell
Method 1 – Merging Path and File Name
We can do this task in another way in Excel VBA. We can merge the path and the file name.
The code to open a workbook from a path in a cell in Excel VBA. Put it in your visual basic module and run it to open the workbook.
Sub MergeCell()
Dim FilePath As String
Dim wb As Workbook
' Get the file path from the cell
my_P = Range("B5").Value
my_F = Range("C5").Value
FilePath = my_P & "\" & my_F
' Check if the file exists
If Dir(FilePath) <> "" Then
' Open the workbook
'Set wb = Workbooks.Open(my_F)
Workbooks.OpenText Filename:=FilePath, DataType:=xlDelimited, Comma:=True
Else
MsgBox "File not found!"
End If
End SubVBA Breakdown
my_P = Range("B5").Value
my_F = Range("C5").Value
FilePath = my_P & "\" & my_FCell B5 contains the file path, and cell C5 contains the file. The values from the cells are assigned to the “my_P” and “my_F” variables. These variables are concatenated with “&” to make the full path of the Excel file.
Workbooks.OpenText Filename:=FilePath, DataType:=xlDelimited, Comma:=TrueThe “Filename” parameter specifies the “FilePath” The data type is “xlDelimited”. It means the text is separated by a delimiter character. The last parameter Comma:=True means in this case, the delimiter is a comma.
Method 2 – Open Workbook Using GetOpenFileDialog
We can also open a workbook using the GetOpenFileName dialog in Excel VBA. This function helps open files in VBA. We will utilize this function to open a workbook from any path through File Explorer.
Copy the following VBA codes and paste them into your Module.
Sub GetOpenFile()
Dim FilePath As String
Dim wb As Workbook
' Get the file path using the GetOpenFilename method
FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
' Check if the user selected a file
If FilePath <> "False" Then
' Open the workbook
Set web = Workbooks.Open(FilePath)
Else
MsgBox "No file selected!"
End If
End SubVBA Breakdown
FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")- The code opens the file dialog box. Then, you can select your desired Excel file from the data stored in the directory.
If FilePath <> "False" Then
' Open the workbook
Set web = Workbooks.Open(FilePath)- This block of code means if the file path is valid then the code will proceed and open the workbook from the file path.
Else
MsgBox "No file selected!"- It will show “ No file selected!” in the msgBox.
How to Open Workbook in Read-Only Mode with Excel VBA
While working in Excel workbooks, we may need to access them in read-only mode to avoid accidentally changing the data or calculations. This is very important when working with sensitive or crucial information. Here we will learn how to open a workbook in read-only mode.
You can copy the VBA code and run it in the module to see the result.
Sub ReadOnly()
Workbooks.Open "C:\Users\Maruf\Desktop\New Workbook.xlsx", , True
End SubVBA Breakdown
Workbooks.Open "C:\Users\Maruf\Desktop\New Workbook.xlsx", , True- The Workbooks.Open method opens the workbook from the given destination. After the path, there are a few parameters. The second parameter is left blank. It means it will open in a new instance of Excel, and the last parameter means it will be in read-only mode.
How to Close a Workbook Using VBA in Excel
We have opened an Excel workbook in multiple ways using VBA. Now, it is also necessary to know how to close an Excel workbook using Excel VBA.
You can copy the VBA codes and paste them into a module.
Sub CloseExcelFile()
Workbooks("New Workbook.xlsx").Close SaveChanges:=False
End SubVBA Breakdown
Workbooks("New Workbook.xlsx").Close SaveChanges:=False- Here, the code closes the workbook.
- SaveChanges:False means if any kind of modification happens before closing the workbook, it will not be saved. If you want to save then replace the “False” with “True”.
Things to Remember
- Make sure that the path in the cell is correct. If the path is wrong then the VBA code will not work correctly.
- If the workbook is already open, then the code will run and nothing will happen. Check that to avoid misunderstanding.
Download Practice Workbook
You can download the practice Excel workbook from the download button below.
Related Articles
- How to Open Workbook and Run Macro Using VBA
- How to Browse for File Path Using Excel VBA
- How to Open Folder and Select File Using Excel VBA
- How to Open File Dialog Default Folder with Excel VBA
