Add spaces to end of field in save as PRN

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi all,
I have a sheet that I need to save to space delimited(PRN). My dilemma
is how to add 12 spaces at the end of a six-character field.
The field is as follows

000123 and in the text file I need "000123 "
I know I can copy and paste into Access and then use the the Export
function and set the fixed-width parameters, but I would prefer to
find a way to do this in Excel.
Any suggestions greatly appreciated.
TIA,
Jim
 
Jim,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportTo12WidthPRN()

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

FName = Application.GetSaveAsFilename

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


With ActiveSheet.UsedRange
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(12), 12)
Next ColNdx
Print #FNum, WholeLine

Next RowNdx

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

End Sub
 
Bernie,
Thanks I'll give this a try.
Jim

Bernie Deitrick said:
Jim,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportTo12WidthPRN()

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

FName = Application.GetSaveAsFilename

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


With ActiveSheet.UsedRange
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(12), 12)
Next ColNdx
Print #FNum, WholeLine

Next RowNdx

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

End Sub
Click to expand...
 
Back
Top