| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Export to a Fixed Width txt file
I'm looking to create a macro that will export my active work sheet to
a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Export to a Fixed Width txt file
This macro may get you started:
http://google.com/groups?threadm=015...0a% 40phx.gbl But depending on what those fields are, you may have to modify the code. Are any dates/times, currency? Numbers with a special format? Text that needs to be right justified? Little Penny wrote: I'm looking to create a macro that will export my active work sheet to a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Export to a Fixed Width txt file
Try this cocde. change file name and path as required. Code dumps data from
columns A to W for all rows in worksheet. Sub fixwidth() Dim Field(24, 2) Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const WriteFileName = "FixedWidth.txt" 'Field Name Start Width Field(0, 0) = 1 Field(0, 1) = 69 Field(1, 0) = 70 Field(1, 1) = 13 Field(2, 0) = 83 Field(2, 1) = 11 Field(3, 0) = 94 Field(3, 1) = 48 Field(4, 0) = 142 Field(4, 1) = 18 Field(5, 0) = 160 Field(5, 1) = 14 Field(6, 0) = 174 Field(6, 1) = 14 Field(7, 0) = 188 Field(7, 1) = 14 Field(8, 0) = 202 Field(8, 1) = 14 Field(9, 0) = 216 Field(9, 1) = 14 Field(10, 0) = 230 Field(10, 1) = 14 Field(11, 0) = 244 Field(11, 1) = 14 Field(12, 0) = 258 Field(12, 1) = 14 Field(13, 0) = 272 Field(13, 1) = 14 Field(14, 0) = 286 Field(14, 1) = 14 Field(15, 0) = 300 Field(15, 1) = 14 Field(16, 0) = 314 Field(16, 1) = 14 Field(17, 0) = 328 Field(17, 1) = 14 Field(18, 0) = 342 Field(18, 1) = 14 Field(19, 0) = 356 Field(19, 1) = 26 Field(20, 0) = 382 Field(20, 1) = 127 Field(21, 0) = 509 Field(21, 1) = 64 Field(22, 0) = 573 Field(22, 1) = 71 Field(23, 0) = 644 Field(23, 1) = 1 Set fswrite = CreateObject("Scripting.FileSystemObject") 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 For ColumnCount = 1 To 24 ColumnText = Cells(RowCount, ColumnCount) ColumnText = ColumnText & _ String(Field(ColumnCount - 1, 1) - _ Len(ColumnText), " ") tswrite.Write ColumnText Next ColumnCount tswrite.WriteLine Next RowCount tswrite.Close End Sub "Little Penny" wrote: I'm looking to create a macro that will export my active work sheet to a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Export to a Fixed Width txt file
Thanks Joel When I try the macro in Excel I get Compile Error Variable not defined On this line of Code Set fswrite = CreateObject("Scripting.FileSystemObject") Any Idea? On Thu, 6 Sep 2007 15:58:00 -0700, Joel wrote: Try this cocde. change file name and path as required. Code dumps data from columns A to W for all rows in worksheet. Sub fixwidth() Dim Field(24, 2) Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const WriteFileName = "FixedWidth.txt" 'Field Name Start Width Field(0, 0) = 1 Field(0, 1) = 69 Field(1, 0) = 70 Field(1, 1) = 13 Field(2, 0) = 83 Field(2, 1) = 11 Field(3, 0) = 94 Field(3, 1) = 48 Field(4, 0) = 142 Field(4, 1) = 18 Field(5, 0) = 160 Field(5, 1) = 14 Field(6, 0) = 174 Field(6, 1) = 14 Field(7, 0) = 188 Field(7, 1) = 14 Field(8, 0) = 202 Field(8, 1) = 14 Field(9, 0) = 216 Field(9, 1) = 14 Field(10, 0) = 230 Field(10, 1) = 14 Field(11, 0) = 244 Field(11, 1) = 14 Field(12, 0) = 258 Field(12, 1) = 14 Field(13, 0) = 272 Field(13, 1) = 14 Field(14, 0) = 286 Field(14, 1) = 14 Field(15, 0) = 300 Field(15, 1) = 14 Field(16, 0) = 314 Field(16, 1) = 14 Field(17, 0) = 328 Field(17, 1) = 14 Field(18, 0) = 342 Field(18, 1) = 14 Field(19, 0) = 356 Field(19, 1) = 26 Field(20, 0) = 382 Field(20, 1) = 127 Field(21, 0) = 509 Field(21, 1) = 64 Field(22, 0) = 573 Field(22, 1) = 71 Field(23, 0) = 644 Field(23, 1) = 1 Set fswrite = CreateObject("Scripting.FileSystemObject") 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 For ColumnCount = 1 To 24 ColumnText = Cells(RowCount, ColumnCount) ColumnText = ColumnText & _ String(Field(ColumnCount - 1, 1) - _ Len(ColumnText), " ") tswrite.Write ColumnText Next ColumnCount tswrite.WriteLine Next RowCount tswrite.Close End Sub "Little Penny" wrote: I'm looking to create a macro that will export my active work sheet to a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Export to a Fixed Width txt file
There must be a Option Explicit statement in your workbook which means all
variables need to be delared with DIM statements. Below are the Dim statements that need to be added Dim fswrite, tswrite Dim WritePathName Dim fwrite Dim LastRow Dim RowCount, ColumnCount Dim ColumnText "Little Penny" wrote: Thanks Joel When I try the macro in Excel I get Compile Error Variable not defined On this line of Code Set fswrite = CreateObject("Scripting.FileSystemObject") Any Idea? On Thu, 6 Sep 2007 15:58:00 -0700, Joel wrote: Try this cocde. change file name and path as required. Code dumps data from columns A to W for all rows in worksheet. Sub fixwidth() Dim Field(24, 2) Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const WriteFileName = "FixedWidth.txt" 'Field Name Start Width Field(0, 0) = 1 Field(0, 1) = 69 Field(1, 0) = 70 Field(1, 1) = 13 Field(2, 0) = 83 Field(2, 1) = 11 Field(3, 0) = 94 Field(3, 1) = 48 Field(4, 0) = 142 Field(4, 1) = 18 Field(5, 0) = 160 Field(5, 1) = 14 Field(6, 0) = 174 Field(6, 1) = 14 Field(7, 0) = 188 Field(7, 1) = 14 Field(8, 0) = 202 Field(8, 1) = 14 Field(9, 0) = 216 Field(9, 1) = 14 Field(10, 0) = 230 Field(10, 1) = 14 Field(11, 0) = 244 Field(11, 1) = 14 Field(12, 0) = 258 Field(12, 1) = 14 Field(13, 0) = 272 Field(13, 1) = 14 Field(14, 0) = 286 Field(14, 1) = 14 Field(15, 0) = 300 Field(15, 1) = 14 Field(16, 0) = 314 Field(16, 1) = 14 Field(17, 0) = 328 Field(17, 1) = 14 Field(18, 0) = 342 Field(18, 1) = 14 Field(19, 0) = 356 Field(19, 1) = 26 Field(20, 0) = 382 Field(20, 1) = 127 Field(21, 0) = 509 Field(21, 1) = 64 Field(22, 0) = 573 Field(22, 1) = 71 Field(23, 0) = 644 Field(23, 1) = 1 Set fswrite = CreateObject("Scripting.FileSystemObject") 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 For ColumnCount = 1 To 24 ColumnText = Cells(RowCount, ColumnCount) ColumnText = ColumnText & _ String(Field(ColumnCount - 1, 1) - _ Len(ColumnText), " ") tswrite.Write ColumnText Next ColumnCount tswrite.WriteLine Next RowCount tswrite.Close End Sub "Little Penny" wrote: I'm looking to create a macro that will export my active work sheet to a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Export to a Fixed Width txt file
That did Joel.
Thank you Thank you Thank you. On Thu, 6 Sep 2007 19:16:00 -0700, Joel wrote: There must be a Option Explicit statement in your workbook which means all variables need to be delared with DIM statements. Below are the Dim statements that need to be added Dim fswrite, tswrite Dim WritePathName Dim fwrite Dim LastRow Dim RowCount, ColumnCount Dim ColumnText "Little Penny" wrote: Thanks Joel When I try the macro in Excel I get Compile Error Variable not defined On this line of Code Set fswrite = CreateObject("Scripting.FileSystemObject") Any Idea? On Thu, 6 Sep 2007 15:58:00 -0700, Joel wrote: Try this cocde. change file name and path as required. Code dumps data from columns A to W for all rows in worksheet. Sub fixwidth() Dim Field(24, 2) Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const WriteFileName = "FixedWidth.txt" 'Field Name Start Width Field(0, 0) = 1 Field(0, 1) = 69 Field(1, 0) = 70 Field(1, 1) = 13 Field(2, 0) = 83 Field(2, 1) = 11 Field(3, 0) = 94 Field(3, 1) = 48 Field(4, 0) = 142 Field(4, 1) = 18 Field(5, 0) = 160 Field(5, 1) = 14 Field(6, 0) = 174 Field(6, 1) = 14 Field(7, 0) = 188 Field(7, 1) = 14 Field(8, 0) = 202 Field(8, 1) = 14 Field(9, 0) = 216 Field(9, 1) = 14 Field(10, 0) = 230 Field(10, 1) = 14 Field(11, 0) = 244 Field(11, 1) = 14 Field(12, 0) = 258 Field(12, 1) = 14 Field(13, 0) = 272 Field(13, 1) = 14 Field(14, 0) = 286 Field(14, 1) = 14 Field(15, 0) = 300 Field(15, 1) = 14 Field(16, 0) = 314 Field(16, 1) = 14 Field(17, 0) = 328 Field(17, 1) = 14 Field(18, 0) = 342 Field(18, 1) = 14 Field(19, 0) = 356 Field(19, 1) = 26 Field(20, 0) = 382 Field(20, 1) = 127 Field(21, 0) = 509 Field(21, 1) = 64 Field(22, 0) = 573 Field(22, 1) = 71 Field(23, 0) = 644 Field(23, 1) = 1 Set fswrite = CreateObject("Scripting.FileSystemObject") 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 For ColumnCount = 1 To 24 ColumnText = Cells(RowCount, ColumnCount) ColumnText = ColumnText & _ String(Field(ColumnCount - 1, 1) - _ Len(ColumnText), " ") tswrite.Write ColumnText Next ColumnCount tswrite.WriteLine Next RowCount tswrite.Close End Sub "Little Penny" wrote: I'm looking to create a macro that will export my active work sheet to a fixed width text file. Currently I import my data from Excel to MS Access and then export from Access as a fixed width txt file. Can I do this with a macro in Excel? I have 24 columns of information and could have a few hundred or a few thousand rows of data. Below is the fixed with specs I use to export from Access. Field Name Start Width Field1 1 69 Field2 70 13 Field3 83 11 Field4 94 48 Field5 142 18 Field6 160 14 Field7 174 14 Field8 188 14 Field9 202 14 Field10 216 14 Field11 230 14 Field12 244 14 Field13 258 14 Field14 272 14 Field15 286 14 Field16 300 14 Field17 314 14 Field18 328 14 Field19 342 14 Field20 356 26 Field21 382 127 Field22 509 64 Field23 573 71 Field24 644 1 Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fixed Column Width Export | Excel Discussion (Misc queries) | |||
| Macro for importing a fixed width text file into the activeworkbook | Excel Programming | |||
| How to export to fixed width text file? | Excel Worksheet Functions | |||
| Importing Fixed Width File Macro | Excel Programming | |||
| Export to fixed width text file | Excel Discussion (Misc queries) | |||
Linear Mode
