Blank last column - converting excel to .prn

[kwaku101]

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.

 

[Alphacsulb]

Is the 3rd column suppose to hold a certain number of positions?

 

[kwaku101]

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]

How many characters do you have in column 1 and 2 right now?

 

[Alphacsulb]

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

 

[kwaku101]

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]

Excel Workbook
	A	B
1	aaaaaaaaaa	bbbbbbbbbb
2	aaaaaaaaaa	bbbbbbbbbb
3	aaaaaaaaaa	bbbbbbbbbb
4	aaaaaaaaaa	bbbbbbbbbb
5	aaaaaaaaaa	bbbbbbbbbb
6	aaaaaaaaaa	bbbbbbbbbb
7	aaaaaaaaaa	bbbbbbbbbb
8	aaaaaaaaaa	bbbbbbbbbb
9	aaaaaaaaaa	bbbbbbbbbb
10	aaaaaaaaaa	bbbbbbbbbb
testcolumn3

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

 

[Alphacsulb]

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]

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.

 

[Alphacsulb]

Your welcome, I'm just happy to be able to help someone else.