Wild cards in VBA

[Dan71]

Good morning all.
I am using this function in VBA

Windows("buying report week 20.xls").select

It works fine but I want to change it so that I can use a wild card as the week number changes. I have tried verious diferent permutations but can't get it to work. I assume it is fairly simple.
Can you help please.
Dan

 

[Richard Schollar]

Hi Dan

A good idea would be to set the workbook name as a string variable and also hold the week number in a separate integer variable then pass this to the Windows collection:

Dim strFile As String, iWeekNum As Integer
iWeekNum = Sheets("Sheet1").Range("A1").Value
strFile = "Buying Report Week" & iWeekNum & ".xls"
Windows(strFile).Activate

You can assign the week value to iWeekNum in a variety of different ways - the above is an example of how you might do so from a worksheet cell.

Hope this helps!

Richard

 

[Dan71]

Thanks Richard, I suppose I wll have to do that. I had hoped that I would not need to put in the week number and keep it really simple. Oh well.
Dan