Macro to Export to a Fixed Width txt file

[Little Penny]

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]

This macro may get you started:
http://google.com/[email protected]

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?

 

[Joel]

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]

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?

 

[Joel]

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]

That did Joel.


Thank you Thank you Thank you.