Creating variables for file path names VBA

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
146
I am writing coding for a rather large macro and I am trying to decrease the number of characters I use. One way to do this is to define a variable for the long file path name but it is not liking it.

My coding is:

' Ask user for the period of the projection exercise
PNumber = InputBox("What period is it?" & vbNewLine & vbNewLine & "Please enter the number:")

'Define variables
FNTemplate = "J:\FinanceAdmin\Finance\Resource Management\Regional\2019-20\Templates\RPA Zone Roll Up"
FNProjection = "J:\FinanceAdmin\Finance\Resource Management\Regional\2019-20\Projections\P" & PNumber & "\Variances\RPA Roll Up"


'Open all Branch Rollup templates
Workbooks.Open Filename:="'FNTemplate'\2020.PX.LPRA Zone Roll up.v1.RXM.xlsx"
Workbooks.Open Filename:="'FNTemplate'\2020.PX.ABSB Zone Roll up.v1.RXM.xlsx"
Workbooks.Open Filename:="'FNTemplate'\2020.PX.Appeals Zone Roll up.v1.RXM.xlsx"
Workbooks.Open Filename:="'FNTemplate'\2020.PX.CVB Zone Roll up.v1.RXM.xlsx"
Workbooks.Open Filename:="'FNTemplate'\2020.PX.DCP Zone Roll up.v1.RXM.xlsx"
Workbooks.Open Filename:="'FNTemplate'\2020.PX.ILBI Zone Roll up.v1.RXM.xlsx"

Am I wrong to assume you can define a variable as a file path?
 

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
365, 2016, 2013
Platform
Windows
Dim FPath as string

FPath = "FNTemplate"

Workbooks.Open Filename:=FPath & "\2020.PX.LPRA Zone Roll up.v1.RXM.xlsx"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
Try
Code:
Workbooks.Open Filename:=FNTemplate & "\2020.PX.LPRA Zone Roll up.v1.RXM.xlsx"
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,830
Office Version
365
Platform
Windows
You just need to build a string by concatenating your string variable and your file name together eg:

Code:
FNTemplate = "J:\FinanceAdmin\Finance\Resource Management\Regional\2019-20\Templates\RPA Zone Roll Up"
FN = "2020.PX.LPRA Zone Roll up.v1.RXM.xlsx"

Workbooks.Open Filename:=FNTemplate & "\" & FN
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
146
Thank everyone!!! I REALLY appreciate!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
146
Now I ran into a different problem but it is similar. I think it may have to do with my brackets as the file name has a data that will change each month:

Coding:

'Ask user for the period of the projection exercise
PNumber = InputBox("What period is it?" & vbNewLine & vbNewLine & "Please enter the number:")

'Define variables

Dim FNProjection As String
FNProjection = "\\S15ACFFP0001\GROUP-D\Finance\Resource Management\Regional\2019-20\Projections\P" & PNumber & "\Variances\RPA Roll Up"

'Save each template in the correct projection period folder

Windows("2020.PX.ILBI Zone Roll up.v1.RXM.xlsx").Activate
ActiveWorkbook.SaveAs Filename:=FNProjection & "\2020.P" & PNumber & ".ILBI Zone Roll up.v1.RXM.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

Windows("2020.PX.DCP Zone Roll up.v1.RXM.xlsx").Activate
ActiveWorkbook.SaveAs Filename:=FNProjection & "\2020.P" & PNumber & ".DCP Zone Roll up.v1.RXM.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
What is the value of PNumber?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
Yes, but what is the value when the code fails?
 

Forum statistics

Threads
1,086,237
Messages
5,388,636
Members
402,127
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top