Creating a fixed width text file with VBA 2

[vbnetnewby1967]

Hello...
I hope someone can help me.
I have a Excel spreadsheet which contains approximately 1700 records. I need to process this file in a certain way and then write data to a file using a fixed width. Not all rows will have the same fixed width format.
Example:
ADIND09012005SPARKS1 LAST_NAME FIRST_NAME
ADIBE09012005SPARKS1ME09012005

Could any please show me how to write this data to a file in a fixed width format? Any help at all would be appreciated.

Ted

 

[macropod]

Hi Ted,

Perhaps the simplest way is to concatenate the data in Excel, using something like:
=LEFT(A1&B1&C1,40)&REPT(" ",MAX(0,40-LEN(A1&B1&C1)))
to pad the data out. In the above example, A1:C1 are combined and padded out the 40 charatcers. If the source data are any longer than that, they get truncated at 40 characters also.

Similarly, you can pad out and truncate the individual columns with something like:
=LEFT(A1,10)&REPT(" ",MAX(0,10-LEN(A1)))&LEFT(B1,15)&REPT(" ",MAX(0,15-LEN(B1)))&LEFT(C1,15)&REPT(" ",MAX(0,15-LEN(C1)))

Cheers

 

[SkipVought]

Not all rows will have the same fixed width format

Then, how do you know what fixed width format is required for what row???

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[JerryKlmns]

I dealt with this like :

Row A : each column cell contains the fixed lenght needed.
Row B : Column names (...... )
then following the 1700 records

A1701=Left("A3" & rept(" ", $a$1), $a$1) this is for text
B1701=Left("B3" & rept(" ", $B$1), $b$1) this is for text
C1701=Right(rept(" ", $c$1)) & "C3", $c$1) this is for numbers
.
.
.
W1701=Left("W3" & rept(" ", $W$1), $w$1) this is for text

AA1701=a1701&b1701&...&w1701

copy column AA, paste values to a new excel and save it as txt.

Keep in mind there is a 32.767 characters limit for a cell!

Not a very professional approach ....

So you 'll need FileSystemObject for writting the file and a loop for all rows to join columns using the same technique for field lenght.

 

[vbnetnewby1967]

Thank you for answering my post. I really appreciate it.
Will the fact that there are 90+ columns cause a problem using this method???
Thanks again for your help

 

[JerryKlmns]

Excel has 255 columns. If your fixed length file is less than 32.767 characters long, then it works!

If not, built a sub in VBA for a more professional way!

 

[macropod]

Hi Ted,

Having 90+columns will cause a problem if you use formulae, because you'll run up against the formula-length limit much sooner than that.

Here's some code you can use instead:
Sub TextFileExport()
'The next row tells Excel where to save the output. Modify as needed, keeping the trailing backslash.
Const FilePath = "C:\"
Dim WkSht As Worksheet, ff As Integer
Dim CurrentRow As Long, CurrentCol As Long
Dim MaxRow As Long, MaxCol As Long
Dim strOutput As String
'Loop through all worksheets.
For Each WkSht In ActiveWorkbook.Worksheets
ff = FreeFile
'Open a text file using the current worksheet’s name in the nominated path.
Open FilePath & WkSht.Name & ".txt" For Output As #ff
MaxRow = WkSht.Range("A65536").End(xlUp).Row
MaxCol = WkSht.Range("IV1").End(xlToLeft).Column
'The next line determines the start & end rows. If using the row 1 to hold the column widths, start at row 2.
For CurrentRow = 1 To MaxRow
strOutput = ""
'The next line determines the start & end columns.
For CurrentCol = 1 To MaxCol
'The next line uses the value in row 1 to determine column widths.
' strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), WkSht.Cells(1, CurrentCol))
'The next line uses the text width in row 1 to determine column widths, and adds a space between them.
'Delete the ‘1+’ if the extra space isn’t needed
'strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), 1 + Len(WkSht.Cells(1, CurrentCol)))
Next CurrentCol
'Write the line to the file.
Print #ff, strOutput
Next CurrentRow
'Close the file.
Close #ff
Next WkSht
Set WkSht = Nothing
End Sub

The above code, which I picked up elsewhere and modified, pads out the charatcers to the pre-defined column widths and truncates any that are longer than the limit. Apart from avoiding formula lentgth limits, the other advantage of this approach is that it also avoids Excel's cell-character limit.

If you want to right-align some values, as suggested by JerryKlmns, it would only take a minor tweak to the code and worksheet(s) to support this.

Cheers