VBA to open file with a variable name

[jack.a]

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!

[Phuocam]

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Try this:

Please Login or Register  to view this content.

[jack.a]

Works perfectly! Thank you!

Please Login or Register  to view this content.

[jack.a]

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

Please Login or Register  to view this content.

[Phuocam]

Chane to:

Please Login or Register  to view this content.