如何关闭仅打开的Excel工作表

Question

我有一个代码，它将打开报告，假设它们在模型运行的同一天在Outlook中收到。

当某些报告未收到时，我面临一个问题，但代码假设所有报告都已收到以关闭工作表。

我如何重写下面的代码，只关闭已打开的报告?

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

Answer 1

此函数确定指定的工作簿当前是否打开并返回True或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

然后在代码中为每个命名的工作簿做这样的事情(在示例中根据需要更改每个工作簿的保存行为):

strName = "VLCC Form BRS.xlsx"
If WorkbookIsOpen(strName) Then
Workbooks(strName).Close SaveChanges:=False
End If