Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Exporting to Text file
I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in columns, is there anywhere I need to put in so if there is only 5 characters it will pad the text out to 12 and therefore keep the text file as if it were in columns? Many thanks Sub WriteFixed() Const MyPath = "C:\temp\" Const WriteFileName = "text.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 = Cells(RowCount, Colcount).Text If Len(Data) < 12 Then Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data)) End If OutPutLine = OutPutLine & Data Next Colcount tswrite.writeline OutPutLine Next RowCount tswrite.Close End Sub "santaviga" wrote: Hi to all. I need some help with exporting excel data to text file, I have data in cells throughout the worksheet, what I need is a macro to export the data in all cells to a text file on the desktop, but these need to be a maximum of 12 characters and if not 12 character it needs to be padded out to 12 characters so that when it exports to a text file the data is displayed as if in colums and not all over the place. Also I need to know how it exports e.g every time I save the file will it export to a text file on desktop? I am currenty doing this a long way by formulas but I think would be better if it was done by a macro. I thankyou in advance Any help would be much appreciated I am new to macros Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Exporting to Text file
I use this simple macro:
Sub SaveAsText() ThisWorkbook.SaveAs Filename:="MyText.txt", _ FileFormat:=xlTextWindows End Sub Regards, Ryan-- -- RyGuy "santaviga" wrote: I have input the following code and it works to an extent, when it runs the macro to export the data to text file, the text is all joined and not in columns, is there anywhere I need to put in so if there is only 5 characters it will pad the text out to 12 and therefore keep the text file as if it were in columns? Many thanks Sub WriteFixed() Const MyPath = "C:\temp\" Const WriteFileName = "text.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 = Cells(RowCount, Colcount).Text If Len(Data) < 12 Then Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data)) End If OutPutLine = OutPutLine & Data Next Colcount tswrite.writeline OutPutLine Next RowCount tswrite.Close End Sub "santaviga" wrote: Hi to all. I need some help with exporting excel data to text file, I have data in cells throughout the worksheet, what I need is a macro to export the data in all cells to a text file on the desktop, but these need to be a maximum of 12 characters and if not 12 character it needs to be padded out to 12 characters so that when it exports to a text file the data is displayed as if in colums and not all over the place. Also I need to know how it exports e.g every time I save the file will it export to a text file on desktop? I am currenty doing this a long way by formulas but I think would be better if it was done by a macro. I thankyou in advance Any help would be much appreciated I am new to macros Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Exporting to Text file
Hi Ryan, That code you gave me not doing anything I need, its creating the
text file but nothing in it and clearing data in wxcel. "ryguy7272" wrote: I use this simple macro: Sub SaveAsText() ThisWorkbook.SaveAs Filename:="MyText.txt", _ FileFormat:=xlTextWindows End Sub Regards, Ryan-- -- RyGuy "santaviga" wrote: I have input the following code and it works to an extent, when it runs the macro to export the data to text file, the text is all joined and not in columns, is there anywhere I need to put in so if there is only 5 characters it will pad the text out to 12 and therefore keep the text file as if it were in columns? Many thanks Sub WriteFixed() Const MyPath = "C:\temp\" Const WriteFileName = "text.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 = Cells(RowCount, Colcount).Text If Len(Data) < 12 Then Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data)) End If OutPutLine = OutPutLine & Data Next Colcount tswrite.writeline OutPutLine Next RowCount tswrite.Close End Sub "santaviga" wrote: Hi to all. I need some help with exporting excel data to text file, I have data in cells throughout the worksheet, what I need is a macro to export the data in all cells to a text file on the desktop, but these need to be a maximum of 12 characters and if not 12 character it needs to be padded out to 12 characters so that when it exports to a text file the data is displayed as if in colums and not all over the place. Also I need to know how it exports e.g every time I save the file will it export to a text file on desktop? I am currenty doing this a long way by formulas but I think would be better if it was done by a macro. I thankyou in advance Any help would be much appreciated I am new to macros Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Exporting to Text file
Hummm, not sure why it is behaving like this; it works fine for me. Did you
check 'My Documents' for the file? I just tested it again; works fine here. Ryan-- -- RyGuy "santaviga" wrote: Hi Ryan, That code you gave me not doing anything I need, its creating the text file but nothing in it and clearing data in wxcel. "ryguy7272" wrote: I use this simple macro: Sub SaveAsText() ThisWorkbook.SaveAs Filename:="MyText.txt", _ FileFormat:=xlTextWindows End Sub Regards, Ryan-- -- RyGuy "santaviga" wrote: I have input the following code and it works to an extent, when it runs the macro to export the data to text file, the text is all joined and not in columns, is there anywhere I need to put in so if there is only 5 characters it will pad the text out to 12 and therefore keep the text file as if it were in columns? Many thanks Sub WriteFixed() Const MyPath = "C:\temp\" Const WriteFileName = "text.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 = Cells(RowCount, Colcount).Text If Len(Data) < 12 Then Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data)) End If OutPutLine = OutPutLine & Data Next Colcount tswrite.writeline OutPutLine Next RowCount tswrite.Close End Sub "santaviga" wrote: Hi to all. I need some help with exporting excel data to text file, I have data in cells throughout the worksheet, what I need is a macro to export the data in all cells to a text file on the desktop, but these need to be a maximum of 12 characters and if not 12 character it needs to be padded out to 12 characters so that when it exports to a text file the data is displayed as if in colums and not all over the place. Also I need to know how it exports e.g every time I save the file will it export to a text file on desktop? I am currenty doing this a long way by formulas but I think would be better if it was done by a macro. I thankyou in advance Any help would be much appreciated I am new to macros Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
Exporting Text File | Excel Programming |