Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add spaces to end of field in save as PRN
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add spaces to end of field in save as PRN
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 "Jim" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add spaces to end of field in save as PRN
Bernie,
Thanks I'll give this a try. Jim "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Jim" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace spaces at the end of a field | Excel Discussion (Misc queries) | |||
Save as CSV: extra spaces around commas | Excel Discussion (Misc queries) | |||
How do I put spaces in a field | Excel Discussion (Misc queries) | |||
How do you remove excess spaces from an Excel field? | Excel Discussion (Misc queries) | |||
Adding spaces to a text field | Excel Programming |