Is it possible to activate a file with a variable name

Question

I'm trying to open a file which could have a different name everyday but will always contain '1234'. After that, copy and paste various rows of data etc. Problem i'm having now is that I used the macro function to record but I'm unable to use windows.activate to switch to the correct files to copy and paste

sPath = "C:\Users\kings\OneDrive\Desktop\operation\"
sWildcard = "*123*.*"
sFile = Dir(sPath & sWildcard)
If sFile <> "" Then
  sWorkbook = sPath & sFile
  Workbooks.Open sWorkbook
Else
  MsgBox "File Not Found"
  ' Exit Sub '// optionaly exit the subroutine so that it does not try to continue //
End If
Windows(sWorkbook).Activate

Getting "runtime error 9 subscript out of range" on Windows(sWorkbook).

That's because `sWorkbook` contains the full path but for `Windows(..).activate` only the file name resp. workbook name without complete path is needed. Anyway, usually `activate` etc. is not needed as you have access to the workbook via the workbook object, please look at [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Storax, Jan 12 '20 at 16:34

score 0 · Answer 1 · answered Jan 12 '20 at 16:42

If you still want to activate the workbook I'd suggest to use the workbook object instead

sPath = "C:\Users\kings\OneDrive\Desktop\operation\"
sWildcard = "*123*.*"
sfile = Dir(sPath & sWildcard)

Dim wkb As Workbook
If sfile <> "" Then
    sWorkbook = sPath & sfile
    Set wkb = Workbooks.Open(sWorkbook)
    wkb.Activate
Else
    MsgBox "File Not Found"
    ' Exit Sub '// optionaly exit the subroutine so that it does not try to continue //
End If

How to avoid using Select in Excel VBA

Is it possible to activate a file with a variable name

1 Answers1