Switch to Open Workbook using a partial name and wildcard

[tljenkin]

Hi All

I have the code below but instead of defining the full name of the workbook which will change as different versions are created, can I get VBA to switch to that workbook using the partial name "Actuals Repository" and a wildcard? OR if possible for excel to just attempt to open the file, realise it is open, dont open another copy and skip the notification that usually pops up asking if you would like to open the file again.Thanks

Sub IsItOpen()
Dim Response As Integer
Response = MsgBox(prompt:="Is the Actuals repository already open?", Buttons:=vbYesNo)
If Response = vbYes Then
Windows("Actuals Repository*.xls").Activate
End If
If Response = vbNo Then
Dim FileToOpen As Variant, File As Variant, MasterWb As Workbook, Wb As Workbook
On Error GoTo 0
Set MasterWb = ThisWorkbook ' or BPM Model latest version presently open
FileToOpen = Application.GetOpenFilename(FileFilter:="All Excel Files (*.xlsm), actuals*.xlsm", Title:="Where is the Actuals Repository?", MultiSelect:=False)
Application.ScreenUpdating = False
' Exit if user exits dialog
If FileToOpen = False Then Exit Sub
' Open file (Wb), copy weekly analysis from Repository (several sheets) to BPM
Set Wb = Workbooks.Open(FileToOpen)
Wb.Activate
End If



End Sub

 

[Richard Schollar]

You can check if such a workbook is open by looping thru the workbooks collection (of open workbooks) and checking the name of each:

Dim wb As Workbook

For Each wb In Application.Workbooks
   If wb.Name Like "Actuals repository*" Then wb.Activate:Exit Sub
Next wb

'if code gets here, it isn't already open...

 

[tljenkin]

I already dimmed wb as workbook, here is what I am about to test, is this correct?

Dim FileToOpen As Variant, File As Variant, MasterWb As Workbook, Wb As Workbook
Set MasterWb = ThisWorkbook ' or BPM Model latest version presently open

Dim Response As Integer
Response = MsgBox(prompt:="Is the Actuals repository already open?", Buttons:=vbYesNo)
If Response = vbYes Then


For Each Wb In Application.Workbooks
If Wb.Name Like "Actuals Repository*" Then Wb.Activate: Exit Sub
Next Wb

Set Wb = ActiveWorkbook

 

[Richard Schollar]

I already dimmed wb as workbook, here is what I am about to test, is this correct?

Dim FileToOpen As Variant, File As Variant, MasterWb As Workbook, Wb As Workbook
Set MasterWb = ThisWorkbook ' or BPM Model latest version presently open

Dim Response As Integer
Response = MsgBox(prompt:="Is the Actuals repository already open?", Buttons:=vbYesNo) '===technically you don't need to ask this MsgBox if you don't want to
If Response = vbYes Then '===unnecessary


For Each Wb In Application.Workbooks
If Wb.Name Like "Actuals Repository*" Then Wb.Activate: Exit Sub '===Sub is exited at this point, so if the workbook is open, no further code will be executed. So the result would simply be the Actuals Repository workbook would be activated
Next Wb

Set Wb = ActiveWorkbook '===not required

See above for my comments (in red).

 

[tljenkin]

Thanks, it works

 

[pamelaneal]

I have a similar problem, but this response did not answer my question. In my situation, I need to switch back and forth between two open workbooks to copy and paste info to a third workbook. In the code, I would like to make part of the file name a wildcard because the filename will change daily, depending on the date. Here are examples:

PPMfloorApril-1-2015.xlsx (would like to use PPMfloor*.xlsx)
PPM Trailer scan 4-1-2015.xlsx (would like to use PPM Trailer scan*.xlsx)

When I do this, I get errors. Is there any way around this?