Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to figure out a way to print a "mainframe" style report using VBA. I am attempting to open the file with Excel but it's being very problematic getting it to format and print properly and I'm wondering if anybody has any existing code or suggestions.

Details: the report I am trying to print is approximately 133 characters wide; the data is formatted into fixed-width columns; the pages are separated by Form Feed characters (ASCII code 12).

What I am looking for is a way to send this report to a printer, in landscape mode, one page wide with all 133 characters on each page, with page breaks at each Form Feed.

If anybody has suggestions other than using Excel, I am open to hearing them also.

Thanks! Nick

share|improve this question

2 Answers 2

If you don't need to format the report, Excel isn't going to help you here.

I would suggest using a decent text editor such as Notepad++

Open the report in the editor, go to the page settings and set to landscape. Adjust the margins to the smallest they will go. You will also need to adjust the zoom level to get everything to fit. Make sure that you have turned *off any "word wrap" feature and do a print preview to see how things fit.

You could also to this in Word at a pinch. Set the page up as above, set the font to something fairly small and fixed pitch (e.g. Lucida Console).

If you get stuck trying to get the text to fit. Install a print-to-PDF utility (there are several free ones, I use Nitro PDF Creator 2. Set the page size to A3 landscape instead of A4, print to PDF and then print the PDF to A4 landscape, it should resize OK. Of course, you might need a magnifying glass!

share|improve this answer

Sorry, I might not have made it clear in my original question that I am looking for a way to do this programmatically.

The best solution that I found was to use the following VBA code to drive Excel. I had to add a pipe character at the beginning of each line so that Excel would not strip the left padding spaces (there is probably a better solution but I couldn't figure it out).

Dim XL As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As New Excel.Worksheet

XL.Workbooks.OpenText myFile, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(133, 9))

Set WB = XL.ActiveWorkbook

Set WS = WB.Worksheets(1)

WS.Columns("A:Z").Select

XL.Selection.Columns.AutoFit
XL.Selection.Font.Size = 10
XL.Selection.RowHeight = 12.5

For r = 1 To WS.UsedRange.Rows.Count
' This adds the page breaks at the Form Feed characters
    If InStr(WS.Cells(r, 1), Chr(12)) Then
        WS.Cells(r, 1).Select
        XL.ActiveWindow.SelectedSheets.HPageBreaks.Add XL.ActiveCell
    End If
Next r

With WS.PageSetup
    .Orientation = xlLandscape
    .LeftMargin = XL.InchesToPoints(1)
    .RightMargin = XL.InchesToPoints(0)
    .TopMargin = XL.InchesToPoints(0.5)
    .BottomMargin = XL.InchesToPoints(0)
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With

WB.PrintOut

WB.Close False
XL.Quit
Set XL = Nothing
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.