Blank last column - converting excel to .prn

kwaku101

New Member
Joined
Mar 13, 2010
Messages
9
I have 3 columns in excel that I need to convert .prn text file. The first 2 have data, the third is blank. In order for the .prn file to work properly, I need it to include the third column which is blank. I cannot find a way to do this. I've got a macro to convert my file to .prn file but it only includes the first two columns which has a total byte length of 20. The third blank column has an additional 10 bytes of width that I need to somehow get onto the .prn file. Any suggestions would be greatly appreciated.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

kwaku101

New Member
Joined
Mar 13, 2010
Messages
9
Hope I'm answering your question.... the third column has no data, but i need to fix the width at 10 bytes. The first 2 columns each has 10 bytes each. When our program reads the .prn file, it will look for 30 bytes. At the moment, my .prn file only shows 20 bytes because I cannot get the third column in.

Thanks
Dan
 

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413

ADVERTISEMENT

You can use:
=Len(A1)
as an example
 

kwaku101

New Member
Joined
Mar 13, 2010
Messages
9
I have 10 in A, and 10 also in B. Additionally, I set the column widths to 10 bytes before converting the excel file to .prn

Thanks,
 

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413

ADVERTISEMENT

Excel Workbook
AB
1aaaaaaaaaabbbbbbbbbb
2aaaaaaaaaabbbbbbbbbb
3aaaaaaaaaabbbbbbbbbb
4aaaaaaaaaabbbbbbbbbb
5aaaaaaaaaabbbbbbbbbb
6aaaaaaaaaabbbbbbbbbb
7aaaaaaaaaabbbbbbbbbb
8aaaaaaaaaabbbbbbbbbb
9aaaaaaaaaabbbbbbbbbb
10aaaaaaaaaabbbbbbbbbb
testcolumn3


10 A's in column A
10 B's in column B
 

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413
Code:
Sub SaveSpaces()

' Puts the Spaces in column C
    Dim i As Long
    LRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = LRow To 1 Step -1
        Range("C" & i).Value = "          " & Chr(160)
        Range("C" & i & ":C" & i).Copy
        Range("C" & i & ":C" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next i
    
' Combines column A B C into Column D
    Dim j As Long
    LRow = Cells(Rows.Count, "C").End(xlUp).Row
    For j = LRow To 1 Step -1
    Range("D" & j).FormulaR1C1 = "=RC[-3]&RC[-2]&RC[-1]"
    Range("D" & j & ":D" & j).Copy
    Range("D" & j & ":D" & j).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next j
    
' Start to delete the columns that are not needed
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    
End Sub

SaveAs Prn file and open it. This code assumes your data is in column A and B. The number of space fillers are 10 for column C. Copy the data to a new worksheet in excel. Copy this macro and test it.
 

kwaku101

New Member
Joined
Mar 13, 2010
Messages
9
I cannot thank you enough. Works like a charm, with a slight change to meet my specific situation. The key was chr(160). It enables the .prn to account for the blank column at the end.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top