Need macro to only close workbook at the end if it wasn't already open when Workbook Open Event runs

[sspatriots]

Hi,

My "Workbook_Open" event calls the code below when I open the workbook. This code in turn will open another workbook temporarily (just long enough to copy some information from it) and then closes it again. I need to be able have the code first check to see if the referenced file is already open when this code is initialized, because if it is I don't want my code to close the reference workbook at the end. I hope this makes sense. Not sure how else to explain this.


Thanks, SS

Sub Refresh_n_ResizeAllPOTables()

Dim wb1, wb2 As Workbook
Dim file_path1 As String

Set wb1 = ThisWorkbook

file_path1 = "H:\Jobs\SPS-PO Block History - DEM-030524-01.xlsm"      'Path to PO Block History - DEM.xlsm
Set wb2 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)     'PO Block History - DEM.xlsm

'Does a lot of stuff here

wb2.Close SaveChanges:=False

End Sub

 

[Jeffrey Mahoney]

Please try this. Below is a snippet of code to check if a workbook is open already. RegOpen stores whether the file was open before running this procedure so I can choose to close it or not at the end. RegFile stores just the filename with the extension.

Dim RegOpen As Boolean
Dim RegWB As Workbook
Dim RegFile As String

  On Error Resume Next
  If wbOpen(RegFile, RegWB) = False Then                          'Check if open already, return open RegWB
    Set RegWB = Workbooks.Open(RegPath, WriteResPassword:="admin")
    RegOpen = False
  Else
    RegOpen = True
  End If
  On Error GoTo 0
  If RegWB Is Nothing Then
    MsgBox "Register file not found"
    Exit Sub
  End If

Function wbOpen(fileName As String, wbO As Workbook) As Boolean
    
    Dim WB As Workbook
    For Each WB In Application.Workbooks
      If WB.Name = fileName Then
        Set wbO = WB
        wbOpen = True
        Exit For
      End If
    Next WB
        
End Function

 

[Jeffrey Mahoney]

In your case you may want to make RegOpen as a public variable to you can use it in other routines. Add to the top of a standard module.
PUBLIC RegOpen as boolean

 

[DanteAmor]

Try this:

Sub Refresh_n_ResizeAllPOTables()
  Dim wb As Workbook, wb1 As Workbook, wb2 As Workbook
  Dim file_path1 As String, sFile As String
  Dim isOpen As Boolean
  
  Set wb1 = ThisWorkbook
  
  sFile = "SPS-PO Block History - DEM-030524-01.xlsm"
  file_path1 = "H:\Jobs\" & sFile      'Path to PO Block History - DEM.xlsm
  
  For Each wb In Workbooks
    If LCase(wb.Name) = LCase(sFile) Then
      isOpen = True
      Exit For
    End If
  Next
  
  If isOpen = False Then
    Set wb2 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)     'PO Block History - DEM.xlsm
  End If
  
    'Does a lot of stuff here
    
  If isOpen = False Then
    wb2.Close SaveChanges:=False
  End If
End Sub

 

[Jeffrey Mahoney]

Hi Dante

 

[sspatriots]

Thanks for all the help on this. Much appreciated.