Create Dynamic File Name Using Named Range

[Johnny Thunder]

Hello All,

I am working on a code and trying to insert a named range into the file name reference but I am getting an error "Constant Expression", not sure what I am doing wrong so maybe someone might spot it.

Sub CountFiles()


Dim xFolder     As String
Dim xPath       As String
Dim xCount      As Long
Dim xFiDialog   As FileDialog
Dim xFile       As String
Dim Year        As String
Dim Month       As String




Set Year = Sheets("Summary").Range("Cyear").Value
Set Month = Sheets("Lookups2").Range("FolderL").Value


xFolder = "\\Wb\acctg\shared\RPTOHP\FP&A\Budgeting & Forecasting\Monthly Management Reports\Testing\" & Year & "\" & Month & "\"

'Do something

End Sub

 

[ranman256]

xFolder is a legal name IF [year] and [month] both have a value.

if either is null, it will fail. But the error does not reflect this. Is this the error line?
put a breakpoint (f9) on the line and evaluate the 2 variables. (hover the cursor over the word after it stops on it)

if both are good values, press F8 to step thru the code.

 

[njimack]

Can't see anything that would give that error, although you shouldn't be using Set for Strings.

Is that your entire code?

 

[Johnny Thunder]

So I tried your comments and added a (msgbox Year) to see if actual text would show up and I am getting my desired result "2018" without the quotes.

When I run the code I am getting the debug highlighting the reference Year and the compile error: "Constant Expression Required"

Not sure what to do now?

xFolder is a legal name IF [year] and [month] both have a value.

if either is null, it will fail. But the error does not reflect this. Is this the error line?
put a breakpoint (f9) on the line and evaluate the 2 variables. (hover the cursor over the word after it stops on it)

if both are good values, press F8 to step thru the code.

 

[ranman256]

the folder MUST exist beforehand.
if not:

MkDir xFolder

(note , to create the folder (the end name) , all the folders above it must exist too)

Public Sub MakeDir(ByVal pvDir)
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(pvDir) Then FSO.CreateFolder pvDir     'MkDir pvDir

Set FSO = Nothing
End Sub

 

[Johnny Thunder]

This update fixed the code! thank you Ranman!

the folder MUST exist beforehand.
if not:

MkDir xFolder

(note , to create the folder (the end name) , all the folders above it must exist too)

Public Sub MakeDir(ByVal pvDir)
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(pvDir) Then FSO.CreateFolder pvDir     'MkDir pvDir

Set FSO = Nothing
End Sub