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