Excel VBA - Open workbook, if not already opened, using wildcard or partial name match

[TropicalMagic]

I would like to open a workbook, if it is not already opened, using a wildcard or partial match of its name.

The only constant name components in the workbook’s name are “Report Due”.



Here are the codes I have tried so far:

```

On Error Resume Next

Dim MyWorkbook As Workbook

Set MyWorkbook = Workbooks("Report Due*.xlsx")

If Err <> 0 Then

On Error GoTo 0

Workbooks.Open ("Report Due*.xlsx")

End If



```

AND

```



Dim MyFileName As Variant

Dim myFolderPath As String



myFolderPath = Application.DefaultFilePath

MyFileName = Dir(myFolderPath & "Report Due*.xlsx")



If MyFileName <> "" Then

Workbooks.Open (myFolderPath & “\” & MyFileName)

End If



```

Both sequences of codes don’t seem to be working as there is an Error 1004: Sorry, we couldn’t find “Report Due*.xlsx”


Many thanks in advance!

 

[Marc L]

​

You must loop on the Workbooks collection to compare each name via the Like operator in order to check if the workbook is yet opened …​

​

For the Dir function it seems you forgot to add a backslash before the name …​