VBA to open file with a variable name

Hey all,

Kinda new at this excel/VBA gig.
Trying to automate some tasks.

The date can be changed manually in the Excel Spreadsheet.

For ex. in this Spreadsheet

B2 is 05
B3 is 27
B4 is 2020

This is the code I'm using to open a file whose name changes based on date.


Sub openvarpt2()

Dim varCellday As Long
Dim varCellmonth As Long
Dim varCellyear As Long


varCellday = Range("B2").Value
varCellmonth = Range("b3").Value
varCellyear = Range("b4").Value

Workbooks.Open "M:\R\2020\EFR - " & varCellmonth & "." & varCellday & "." & varCellyear & ".xlsm"

End Sub

the file path is: M:\R\2020\EFR - 05.27.2020.xlsm

I keep getting an error msg because the "05" gets picked up by the code as only a "5". I formatted it to text already in Excel.

By the way, it works when i change the code to:

Workbooks.Open "M:\R\2020\EFR - " & "0" & varCellmonth & "." & varCellday & "." & varCellyear & ".xlsm"

Any thoughts?

Thanks!

is there any reason you can think of that if i apply the same code to save the file as something specific the formatting would turn it all into zeroes?

the file saves as - "EFR -00.00.0"

filepath: M:\R\2020\EEFR -00.00.0.xlsm

cells

F2 - 5
F3 - 27
F4 - 2020

Sub saveasrisk()

'CHANGE to last biz date

Dim saveCellday As Long
Dim saveCellmonth As Long
Dim saveCellyear As Long

 
 saveCellmonth = Range("F2").Value
 saveCellday = Range("F3").Value
 saveCellyear = Range("F4").Value
 
 ChDir "M:\R\2020"
    ActiveWorkbook.saveas Filename:= _
        "M:\R\2020\EFR - " & Format(saveCellmonth, "00") & "." & Format(saveCellday, "00") & "." & saveCellyear & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub