how to close only open Excel Sheet

Question

I have a code that will open reports given that they are received in Outlook same day as model runs. This works fine.

I face a problem when some of the reports are not received, but the code assumes all are received to close the sheet.

How can I re-write code below, to only close the reports that have been opened?

Tx

Workbooks("VLCC Form BRS.xlsx").Close SaveChanges:=False
Workbooks("VLCC Form Clarkson.xlsx").Close SaveChanges:=False
Workbooks("VLCC Form Galbraiths.xlsx").Close SaveChanges:=False
Workbooks("VLCC Form Gibsons.xlsx").Close
Workbooks("Report VLCC.xlsx").SaveAs ("https://X)
Workbooks("Report VLCC - " & Format(Now(), "DD.MM.YY") & ".xlsx").Save
Workbooks("Report VLCC - " & Format(Now(), "DD.MM.YY") & ".xlsx").Close

I'm not sure I've understood the problem correctly, but you can try to make the log for opening and closing events of each of your workbooks in some file and then check if the file was opened the same day — IvanSTV, Apr 03 '23 at 12:53
[Detect whether Excel workbook is already open](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open). — BigBen, Apr 03 '23 at 12:57
Or there is one more idea- when you open workbook from Outlook, then the file path is some temporary folder - before close you check the current file path - if it is partly a temporary folder, then it was received from Outlook — IvanSTV, Apr 03 '23 at 12:59
I was hoping it was possible to write something like, if workbooks (X1) is open then close, and if not open check workbook (X2) and so on? — André, Apr 03 '23 at 13:13
Why not simply switch off error handling by using `On Error Resume Next` while closing the files? Just add `On Error Goto 0` at the end of the code so that error handling is enabled again — FunThomas, Apr 03 '23 at 14:13
It's usually considered good practice to leave error handling on unless turning it off is the only way to do what you need to do. This case has alternatives that do not require turning it off. — RichardCook, Apr 04 '23 at 16:29

score 0 · Answer 1 · answered Apr 04 '23 at 16:24

This function determines whether the named workbook is currently open and returns True or False:

Function WorkbookIsOpen(ByVal strName As String)
    Dim wkbkCurrent As Workbook, bFound As Boolean
    
    bFound = False
    
    For Each wkbkCurrent In Workbooks  ' Look at all open workbooks
        If wkbkCurrent.Name = strName Then  ' Found our target
            bFound = True
            Exit For  ' Stop looking
        End If
    Next wkbkCurrent
    
    WorkbookIsOpen = bFound
    
End Function

Then do something like this in your code for each named workbook (change the Save behavior as needed per workbooks in your example):

strName = "VLCC Form BRS.xlsx"

If WorkbookIsOpen(strName) Then
    Workbooks(strName).Close SaveChanges:=False
End If

how to close only open Excel Sheet

1 Answers1