S
santaviga
Hi,
I have a macro that when run exports the data in the workbook to a text
file, is there anything I can add to this code so that when I press save it
automatically runs the macro and saves a version of the text file to my
desktop. The code I have is below.
Regards
Sub WriteFixed()
Const MyPath = "C:\documents and settings\Mark\desktop\"
Const WriteFileName = "Trial1.txt"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Trim(Cells(RowCount, Colcount).Text)
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
Else
Data = Left(Data, 12)
End If
If OutPutLine <> "" Then
OutPutLine = OutPutLine & " "
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount
tswrite.Close
End Sub
I have a macro that when run exports the data in the workbook to a text
file, is there anything I can add to this code so that when I press save it
automatically runs the macro and saves a version of the text file to my
desktop. The code I have is below.
Regards
Sub WriteFixed()
Const MyPath = "C:\documents and settings\Mark\desktop\"
Const WriteFileName = "Trial1.txt"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Trim(Cells(RowCount, Colcount).Text)
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
Else
Data = Left(Data, 12)
End If
If OutPutLine <> "" Then
OutPutLine = OutPutLine & " "
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount
tswrite.Close
End Sub