Excel宏，如果满足特定条件，则保存所有打开的工作簿

2 回答

• 1

  你有VBA的经验吗？我使用宏录制器来弄清楚如何保存 . 我做了以下代码并测试了它，它对我来说很好 . 我尝试了两次运行它，它提示我是否要覆盖该文件 . 但是没有内置的其他错误处理 .

  Sub SaveWorkbooks()
  
  Dim WB As Workbook
  Dim FileName As String, FolderPath As String
  Dim SaveWorkbook As Boolean
  
  FolderPath = "Y:\risk"
  ChDir FolderPath
  
  For Each WB In Workbooks
      Select Case WB.Sheets(1).Range("A1").Value
          Case Is = "Currency"
              FileName = "CCY"
          Case Is = "Interest"
              FileName = "IR"
          Case Else
              FileName = ""
      End Select
      'Make sure it's not saving not applicable workbooks
      If FileName <> "" Then SaveWorkbook = True Else SaveWorkbook = False
  
      If SaveWorkbook = True Then
          ActiveWorkbook.SaveAs FileName:=FolderPath & "\" & FileName & ".xlsx", _
              FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
      End If
  
  Next WB
  
  End Sub
  

  回复于 2024-05-17T19:48:54+08:00
• 1

  我同意，@thecoshman这伤害了我的眼睛阅读 . 这一次我会例外 . 试试这个：

  Sub SaveWorkbooks()
  Dim workbookObj As Workbook
  
  For Each workbookObj In Excel.Workbooks
      If workbookObj.Sheets("Book1").Range("A1").Value = "whatever" Then
          workbookObj.Save
          workbookObj.Close
      End If
  Next workbookObj
  
  'Clean up
  Set workbookObj = Nothing
  End Sub
  

  回复于 2024-05-17T19:48:54+08:00