Code for running macro on save

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top