Running macro by click the "Save" button

[Browning Zed]

I ask for help with solving the issue of modifying the macro. The code posted below will export workbook sheet data to text files. Is it realistic to make this macro run when the save button is clicked?

Sub ExportToText()

Dim sh As Worksheet
    Dim newFolder As String
    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
    Dim rowCount As Long

    strFile = Replace(ThisWorkbook.Name, ".xlsm", "")
    newFolder = ThisWorkbook.Path & "\" & strFile
    lFnum = xlCSV

    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(newFolder) Then .CreateFolder newFolder
    End With

    For Each sh In ThisWorkbook.Worksheets
    sFname = newFolder & "\" & sh.Name & ".txt"
        rowCount = 1
    
        Open sFname For Output As lFnum
        For Each rRow In sh.UsedRange.Rows
            If rowCount > 1 And Not IsEmpty(rRow.Cells(1, 1).Value) Then
    
                For Each rCell In rRow.Cells
                    sOutput = sOutput & rCell.Text & ";"
                Next rCell
                sOutput = Left(sOutput, Len(sOutput) - 1)
                Print #lFnum, sOutput
                sOutput = ""
            End If
            rowCount = rowCount + 1
        Next rRow
        Close #lFnum
    Next sh

End Sub

 

[sykes]

In your VBA project, select the "ThisWorkbook" module.
Top left drop-down, select "Workbook"
Top right drop-down, select "BeforeSave"
Put any code in there, that you wish to run when the user saves the WB.

EG:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call ExportToText
End Sub

 

[rlv01]

There is a workbook-level event called BeforeSave that might do what you want.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "This is the BeforeSave Event"
    Call ExportToText
End Sub

 

[Browning Zed]

Thanks for the help guys! Everything worked out.

 

[sykes]

Pleasure. Thanks for the feedback.