Can you have an Excel macro to automatically save a copy in CSV format

Question

Downloading an XLS file is a bit of a pain for the fast application that we put together (we know how to do it, but it does not cost much time in C ++), so we are going to use a simple approach; the user can export a CSV copy. However, to save them, I wondered if we can have a macro that automatically saves the CSV version whenever they save XLS (X) in Excel 2007?

Update: After Timores answered, I dug a little and came up with the following:

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

This works, except that I cannot force it to save the CSV, and not ask me if I want to overwrite even after adding ConflictResolution:=xlLocalSessionChanges

Answer 1

:

VB Excel "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 = > , .

Answer 2

XL, Application.DisplayAlerts = False ( reset True )

Answer 3

OP , -, , , " "? ? ? ? ? ? alert, , script , :

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
        .DisplayAlerts = 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
        .DisplayAlerts = True
    End With
End Sub