Insert prompt in Excel Vba to determine filename t

Question

Let's say I have the following files (one for each week in a year):

• test 01.xlsm
• test 02.xlsm
• test .....xlsm
• test 52.xlsm

I want to be able to choose the file I'd like to open with a prompt.

So instead of this function ...

Workbooks.Open Filename:= _
    "F:\mydocs\test11.xlsm"

... I need something that lets me enter the number myself (so in this case "11" or whatever value between 01 and 52 depending on the week I want to see the results for).

Is this possible? :s

Answer 1

You should use an InputBox:

weekNum = InputBox("Input week number:")

Answer 2

Why not use the file picker?

Dim wb as Workbook

ChDir "F:\mydocs\"

Set wb = Application.GetOpenFilename("Microsoft Excel Files, *.xls*")

This way you can just choose the file.

Answer 3

Perhaps:

Sub duraln()
Dim s As String
s = Application.InputBox(Prompt:="enter two digit suffix", Type:=2)
Workbooks.Open Filename:= _
    "F:\mydocs\test" & s & ".xlsm"
End Sub