excel - 您可以使用 Excel 宏自动将副本另存为 CSV 吗

Question

对于我们正在组装的快速应用程序来说，加载 XLS 文件有点麻烦(我们知道如何做到这一点，但不值得花时间，尤其是在 C++ 中)，因此我们将采用简单的方法让用户导出 CSV 副本。然而，为了省去他们的麻烦，我想知道我们是否可以有一个宏，每当他们在 Excel 2007 中保存 XLS(X) 时，它就会自动保存 CSV 版本？

更新:根据 Timores 的回答，我深入研究并得出了以下结论:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook
    TempFileName = Sourcewb.FullName + ".csv"

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    'Save the new workbook and close it
    With Destwb
        .SaveAs Filename:=TempFileName, FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges
        .Close SaveChanges:=False
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

这可行，但我无法强制保存 CSV，而不是询问我是否要覆盖，即使在添加 ConflictResolution:=xlLocalSessionChanges

Answer 1

原始版本:

在 Excel 的 VB 编辑器部分中，选择左侧导航菜单中的“ThisWorkbok”。在右侧的编辑器中，选择左侧下拉列表中的“工作簿”，然后选择右侧下拉列表中的“保存前”。

将宏替换为:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ActiveWorkbook.SaveCopyAs ActiveWorkbook.FullName + ".csv"
End Sub

这将使用 CSV 扩展名制作副本。

请注意，XLSX 文件不能包含宏(您需要 XLSM 扩展名或较旧的 XLS 扩展名)，并且用户需要具有中级或低级安全性才能运行宏(或者您需要必须签署该文件)。

编辑版本:

看到下面的评论后，我再次测试了它。奇怪的是，它并没有像第一次那样起作用。这是一个固定版本。同样，在宏编辑器的“本工作簿”部分:

Dim fInSaving As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If fInSaving Then
    Exit Sub
End If

fInSaving = True

Dim workbookName As String
Dim parentPath As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

If SaveAsUI Then

    Dim result
    result = Application.GetSaveAsFilename

    If VarType(result) = vbBoolean Then
        If CBool(result) = False Then
            Exit Sub ' user cancelled the dialog box
        End If
    End If

    workbookName = fs.GetFileName(result)
    parentPath = fs.GetParentFolderName(result)
Else

    workbookName = ActiveWorkbook.name
    parentPath = ActiveWorkbook.path
End If


Dim index As Integer
index = InStr(workbookName, ".")

Dim name As String
name = Left(workbookName, index - 1)

' extension can be empty is user enters simply a name in the 'File / Save as' dialog
' so it is not computed (but hard-coded below)

' do not ask for confirmation to overwrite an existing file
Application.DisplayAlerts = False

' save a copy
ActiveWorkbook.SaveAs fs.BuildPath(parentPath, name & ".csv"), XlFileFormat.xlCSV

' Save the normal workbook in the original name
ActiveWorkbook.SaveAs fs.BuildPath(parentPath, name & ".xlsm"), XlFileFormat.xlOpenXMLWorkbookMacroEnabled
Cancel = True

Application.DisplayAlerts = True
fInSaving = False
End Sub

Private Sub Workbook_Open()

    fInSaving = False
End Sub

令人惊讶的是，调用 ActiveWorkbook.SaveAs 会再次触发宏 => 全局 bool 值以防止无限递归。