CODEWITHSUNDEEP
excelvba
Palash Gangal
Palash Gangal

Reputation: 11

How to create a semi changing file path

I'm using a macro code with a fixed file path. I want to change the code in such a manner that most of the path remains fixed, only some part needed to be changed. I want it to pick it from my excel cell.

Code for file path:

workbooks.open Filename:="Z:\"some folder Name"\P04 (another folder name)\P04 xyz.xlsx

In above code I want to change 04 at both places when every month. My plan is I want it to pick 05, 06, 07 and so on from a excel cell. How can I do it?

Upvotes: 0

Views: 25

Answers (1)

CLR
CLR

Reputation: 12289

There are many ways of doing this - but this works:

Dim fname As String
Dim monthno As Long

monthno = Sheets("YourSheetName").Range("A1").Value
fname = "Z:\some folder Name\P" & Format(monthno, "00") & " (another folder name)\P" & Format(monthno, "00") & " xyz.xlsx"
workbooks.Open fname

Upvotes: 1

Related Questions