CODEWITHSUNDEEP
vbaexcelprompt
Stan
Stan

Reputation: 963

Insert prompt in Excel Vba to determine filename to open

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

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

Upvotes: 0

Views: 10687

Answers (3)

David Zemens
David Zemens

Reputation: 53653

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.

Upvotes: 2

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

You should use an InputBox:

weekNum = InputBox("Input week number:")

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96773

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

Upvotes: 1

Related Questions