Running macro by click the "Save" button

Browning Zed

New Member
Joined
Aug 22, 2021
Messages
5
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?
VBA Code:
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
 
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:
VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call ExportToText
End Sub
 
Upvote 0
Solution
There is a workbook-level event called BeforeSave that might do what you want.

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

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top