Creating variables for file path names VBA

[SAMCRO2014]

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]

Dim FPath as string

FPath = "FNTemplate"

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

 

[Fluff]

Try

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

 

[steve the fish]

You just need to build a string by concatenating your string variable and your file name together eg:

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]

Thank everyone!!! I REALLY appreciate!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[SAMCRO2014]

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]

What is the value of PNumber?

 

[SAMCRO2014]

What is the value of PNumber?

PNumber is going to change every month - it stands for period #. Period 2 is May, Period 3 is June, etc.

 

[Fluff]

Yes, but what is the value when the code fails?