Fixed Length, Text Formatted

[Novice2000]

Hello,

We send data to another company as a comma separated txt file. Now I am
being told to switch to the fixed length format (*.txt) instead of the comma
separated format (*.csv).

I am not sure how to go about this. Any suggestions are greatly welcomed.

I am starting with a plain excel export from access. And, I have been given
fixed character lengths to apply. Other postings here suggest using the
Courier New font (which is fixed-width - all characters), then setting the
column widths for the character count in each column. Then use Save As with
the prn file type.

How do you set the character count? Do you use Conditional Formatting or
Data Validation? And if so how can you tell if this has been applied?

Then how do you convert the prn file to *txt?

 

[Bernie Deitrick]

Try the macro below. All values as shown in the activesheet will be padded to 25 characters wide,
so that everything will line up nicely.

HTH,
Bernie
MS Excel MVP

Sub ExportTo25WidthColumnsPRN()

Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim myW As Integer

myW = 25


FName = Application.GetSaveAsFilename( _
Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".prn")

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Intersect(ActiveSheet.UsedRange, Rows("1:65536"))
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Space(myW), myW)
Next ColNdx
Print #FNum, WholeLine

Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub

 

[David Biddulph]

To answer your last question, prn is text, so just rename from prn to txt.