Using a cell value as a string in VBA

[SpearC1993]

Hello all,

Back again for another question!

Can I use a cell value in VBA to open a file for example:

Set January = Workbooks.Open("C:\Users\"CELL A1 HERE".xls") <- this is a guess

In my work sheet have a cell like such

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January_3047[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The result would then be that the macro would open an excel in the location "C:\Users\January_3047.xls". At the moment I have statically written these in but I dont want to write over this code every month.

 

[Jonmo1]

Try this, adjusting 'Sheetname' to the name of the sheet where A1 holds your bookname.

Dim MyBook As String
MyBook = Sheets("Sheetname").Range("A1").Value
Set January = Workbooks.Open("C:\Users\" & MyBook & ".xls")

 

[SpearC1993]

Thank you! Such a quick response, thank you very much

 

[Jonmo1]

You're welcome.