CODEWITHSUNDEEP
excelvba
Davide
Davide

Reputation: 37

How to open an excel workbook with path and file name as changing parameters

I am new with VBA and I am struggling when trying to run a simple command.

I want to open a workbook (name = test.xlsx) which is saved in a specific path (C:\Users\u16086\Desktop\Folder) in my PC with a VBA code.

I defined two names in my first excel that are two parameters that I can modify if the path or the name of the file is different:

path: C:\Users\u16086\Desktop\Folder

file_name: test.xlsx

The code I wrote is:

Option Explicit

Sub openworksheet()
    Dim path As String
    Dim file_name As String
    Workbooks.Open Filename:=path & file_name
End Sub

I ran the code, but it says there’s an error run-time error1004. How do I solve this problem?

Upvotes: 1

Views: 8742

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

You have defined two variables. You must allocate values to them and then build the path as shown below:

Sub openworksheet()
    Dim path As String
    Dim file_name As String
    path = "C:\Users\u16086\Desktop\Folder"
    file_name = "test.xlsx"
    Workbooks.Open fileName:=path & "\" & file_name
End Sub

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149335

The path C:\Users\u16086\Desktop\Folder\test.xlsx can also be written as

"C:\Users\u16086\Desktop\Folder" & "\" & "test.xlsx"

so try 

Workbooks.Open Filename:=path & "\" & file_name

Alternatively, you can check if the folder path ends with "\" or not. For example

Option Explicit

Sub openworksheet()
    Dim path As String
    Dim file_name As String

    '~~> Change Sheet name and cell address accordingly
    path = Sheets("Sheet1").Range("A1").Value
    file_name = Sheets("Sheet1").Range("A2").Value

    If Right(path, 1) <> "\" Then path = path & "\"

    Workbooks.Open Filename:=path & file_name
End Sub

Upvotes: 1

Related Questions