is there a way to open a workbook when its name is a variable?

Question

The title might be confusing.

What I meant was, say I have the path and file saved under a variable.

sPath = "C:\Users\"

sFile = "*1234*.*"

sWorkbook = sPath & sFile

The idea is that the sFile should be as vague as possible to account for any file names which may come through this folder. However, now that I have the file name saved under the sWorkbook variable, I have no idea how to open it by referencing sWorkbook; e.g. workbooks.open("sWorkbook").

https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba/10382861#10382861 — Siddharth Rout, Jan 13 '20 at 00:47

braX · Answer 1 · 2020-01-12T18:45:14.313

1

Use the Dir function to get the filename you want

sPath = "C:\Users\"
sWildcard = "*1234*.*"
sFile = Dir(sPath & sWildcard)
If sFile <> "" Then
  sWorkbook = sPath & sFile
  Workbooks.Open sWorkbook
Else
  Msgbox "File Not Found"
  ' Exit Sub '// optionally exit the subroutine so that it does not try to continue //
End If

edited Jan 12 '20 at 18:45

answered Jan 12 '20 at 08:51

braX

11,506
5
20
33

I would leave that up to the OP. Perhaps it is a CSV file. – braX Jan 12 '20 at 10:07

is there a way to open a workbook when its name is a variable?

1 Answers1