'关闭所有工作簿Sub CloseAllWorkbooks() Dim WB As Workbook SaveAll For Each WB In Workbooks If WB.Name <> ThisWorkbook.Name Then WB.Close savechanges:=True End If Next WB ThisWorkbook.Close savechanges:=TrueEnd Sub
'关闭所有非活动工作簿Sub CloseAllInactiveWorkbooks() Dim WB As Workbook Dim strWb As String strWb = ActiveWorkbook.Name SaveAll For Each WB In Workbooks If WB.Name <> strWb Then WB.Close savechanges:=True End If Next WB Application.StatusBar = "关闭所有非活动工作簿."End Sub
'保存所有工作簿Sub SaveAll() Dim WB As Workbook For Each WB In Workbooks WB.Save Next WB Application.StatusBar = "已保存所有工作簿."End Sub
'返回所有打开的工作簿的数量'参数VisibleOnly：如果设置为True则仅统计可见工作簿数;设置为False则统计所有工作簿数Function WorkbookCount(Optional VisibleOnly As Boolean) As Long Dim WB As Workbook Dim N As Long
For Each WB In Application.Workbooks If VisibleOnly = False Or (VisibleOnly = True And WB.Windows(1).Visible = True) Then N = N + 1 End If Next WB WorkbookCount = NEnd Function
'返回工作簿的名称或者完整名称(即包含路径)'参数R：如果指定则返回包含R的工作簿的名称;忽略则返回调用该函数的工作簿的名称'参数FullName：如果忽略或设置为False,则返回名称;如果设置为True，则返回完整名称Function WorkbookName(Optional R As Range, Optional FullName As Boolean) As String If R Is Nothing Then If FullName = False Then WorkbookName = Application.Caller.Worksheet.Parent.Name Else WorkbookName = Application.Caller.Worksheet.Parent.FullName End If Else If FullName = False Then WorkbookName = R.Worksheet.Parent.Name Else WorkbookName = R.Worksheet.Parent.FullName End If End IfEnd Function
'返回包含工作簿名称的字符串数组'参数FullName：如果设置为True,则返回工作簿的(包含路径)完整名称;设置为False,则只返回工作簿名称'参数SavedOnly：如果设置为True,仅返回已经保存过的工作簿名称;设置为False,则返回所有工作簿名称'参数VisibleOnly：如果设置为True,仅返回可见工作簿名称;设置为False,返回所有工作簿名称Function WorkbookNames(Optional FullName As Boolean, _ Optional SavedOnly As Boolean, _ Optional VisibleOnly As Boolean) As String() Dim Arr() As String Dim N As Long Dim WB As Workbook
If Application.Caller.Rows.Count > 1 And _ Application.Caller.Columns.Count > 1 Then WorkbookNames = CVErr(xlErrRef) Exit Function End If
N = Application.WorksheetFunction.Max( _ Application.Workbooks.Count, Application.Caller.Cells.Count) ReDim Arr(1 To N) N = 0 For Each WB In Application.Workbooks If SavedOnly = False Or (SavedOnly = True And Len(WB.Path) > 0) Then If VisibleOnly = False Or _ (VisibleOnly = True And WB.Windows(1).Visible = True) Then N = N + 1 If FullName = True Then Arr(N) = WB.FullName Else Arr(N) = WB.Name End If End If End If Next WB WorkbookNames = ArrEnd Function
'返回工作簿路径'参数R：如果指定则返回包含R的工作簿的路径,否则返回调用该函数的工作簿的路径Function WorkbookPath(Optional R As Range) As String If R Is Nothing Then WorkbookPath = Application.Caller.Worksheet.Parent.Path Else WorkbookPath = R.Worksheet.Parent.Path End IfEnd Function
'返回自定义或者内置文档属性Function GetProperty(p As String) Dim S As Variant On Error Resume Next S = ActiveWorkbook.CustomDocumentProperties(p) If S <> "" Then GetProperty = S Exit Function End If On Error GoTo EndMacro GetProperty = ActiveWorkbook.BuiltinDocumentProperties(p) Exit FunctionEndMacro: GetProperty = ""End Function
注：本文学习整理自cpearson.com，供有兴趣的朋友参考。
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。
