Excel VBA to Open Workbook from Path in Cell

Get FREE Advanced Excel Exercises with Solutions!

We can access a workbook in Excel from a path specified in a cell. This is useful when we want to dynamically refer to a file path that may change over time. It will also help when users enter the file path in Excel. This article will discuss how to use Excel VBA to Open Workbook from Path in Cell.

The overview video shows the process of opening the workbook from the path in the cell.


How to Launch VBA Macro Editor in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. You can follow these simple steps to open the VBA editor too.

Steps:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Launching Visual Basic Editor from the Developer Tab

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write the VBA code.

Selecting a New Module from the Visual Basic Editor Window


Excel VBA to Open Workbook from Path in Cell: Do It with a Simple Code

It is a simple task to open a workbook in Excel. We can open the workbook from 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 is mentioned below.

Dataset of Open Workbook Using Path in Cell

  • At first, we put the file name in cell B5 and the file path in cell C5.
  • Then, we click the Developer tab and open the Visual Basic editor.
  • Next, run the below-mentioned code in a module.
  • It will open the “New Workbook” file.

VBA Code to Open Workbook From Path in Cell

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 Sub

🔎 VBA Breakdown

Sub OpenWorkbookFromCell()
  • First, we create a subprocedure named “OpenWorkbookFromCell”.
Dim FilePath As String
Dim wb As Workbook

Then, we declare two variables named “FilePath” and “wb” as strings.

FilePath = Range("C5").Value

Here it 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) <> "" Then

The Dir function determines whether the file indicated by “FilePath” exists. The code inside the If block will run if the file is there. If not, the otherwise block’s code will be carried out.

 Set wb = Workbooks.Open(FilePath)

Now we 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.

Read More: How to Open Workbook from Path Using Excel VBA


Some Alternate Ways of Using Excel VBA to Open Workbook from Path in Cell

Aside from the previous methods described above, you can also follow different approaches to open workbooks from the path. However, keep in mind that opening them from cell values can only work with the previous methods. The methods we are describing below are generic codes that contain the path within VBA.


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. Then we can simply open the workbook.

VBA Code to Open Worbook from Merged Path

Here is 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 Sub

🔎 VBA Breakdown

my_P = Range("B5").Value
    my_F = Range("C5").Value
    FilePath = my_P & "\" & my_F

Here, cell 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. Both these variables are concatenated with “&” to make the full path of the Excel file.

Workbooks.OpenText Filename:=FilePath, DataType:=xlDelimited, Comma:=True

The “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.

Read More: How to Open Workbook with Variable Name with Excel VBA


2. Open Workbook Using GetOpenFileDialog

We can also open a workbook using the GetOpenFileName dialog in Excel VBA. This function will help to open files in VBA. We will utilize this function to open a workbook from any path through file explorer.

VBA Code to Open Workbook Using Filedialog

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 Sub

🔎 VBA Breakdown

FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
  • Here, the code opens the file dialog box. Then you can select your desired Excel file from the stored data 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!"
  • Otherwise, it will show “ No file selected!” in the msgBox.

Read More: Excel VBA to Open Workbook in Background


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.

VBA Code to Open 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 Sub

🔎 VBA 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 few parameters, the 2nd 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.

Read More: How to Open Workbook as Read-Only with Excel VBA


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.

VBA Code to Close Workbook

You can copy the VBA codes and paste them into a module.

Sub CloseExcelFile()
Workbooks("New Workbook.xlsx").Close SaveChanges:=False
End Sub

🔎 VBA 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.


Conclusion

In this article, we showed how to open a workbook from the path in a cell in Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Hasan
Maruf Hasan

Maruf Hasan, a BSc Electrical & Electronic Engineering graduate from Ahsanullah University of Science & Technology, boasts over a year of service as an Excel & VBA Content Developer at Exceldemy. He authored 30+ insightful articles and offers solutions to diverse Excel challenges. Maruf's exceptional content reflects his passion for Microsoft Office, problem-solving, and writing. Committed to simplifying complex processes, he significantly contributes to Exceldemy and is deeply enthusiastic about continuous learning in Microsoft Office Suite and data... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo