Creating variables for file path names VBA

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
145
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
32,270
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,792
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
145
Thank everyone!!! I REALLY appreciate!!!
 

Fluff

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

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
145
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
32,270
Office Version
365
Platform
Windows
What is the value of PNumber?
 

Fluff

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

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top