Print a report from Excel VBA

[lawsonbooth]

I am an old programmer who is new to Excel VBA. I have been searching for several days and cannot find an answer to my question. If I have missed the answer please forgive and point me to the thread.

The question: Can I print a report from Excel using VBA directly to the printer like I could in the programming language COBOL for example where I control the print out with format statements, etc.?

[Zack Barresse]

Hello lawsonbooth, welcome to VBAX!!

This depends on what you're talking about. If you are talking about just a single sheet, you can use something like this...

[vba]Sheets("Sheet1").Printout copies:=1[/vba]

If you are talking about something else, you'll probably need to elaborate a little more as to what you are talking about exactly. First thing I thought of was an access report. It could be a dozen other things as well. So if this doesn't help, post with many details.

[lawsonbooth]

Thanks for your help. Moving from the world of procedural programming to the world of MS Excel VBA programming is challenging to say the least.

In the following example, I want to send this data directly to the application printer not a file.

I can not use activesheet.printout, etc. because the sheet has 50 columns and I only want columns 1 and 2.

Example Code:
[vba]
Sub PrintRecords()

Open ?Report1? For Output As #1
Print #1, "Heading Line 1"
Print #1, "Heading Line 2"


For n = 1 to 25
Print #1, Cells(n, 1); Tab(25); Cells(n, 2)
Next n


Print #1, "?
Close #1

End Suib
[/vba]

[lucas]

Excel is different than Access. It's more of a what you see in printpreview is what you get type of application. You can format things before print though and you can copy the info to another sheet to print and then either do away with the print sheet or clear it for another print operation...see attachment. Others may have better ideas.

[lawsonbooth]

Thanks to all for your help! This is a great forum and I get a lot of good help from here.

I looked at your attachment and understand. I have got to learn to think like Excel in worksheet frame of mind.

Thanks again.

[lucas]

If you have questions post here and be sure to let us know how it goes.

[Zack Barresse]

You can specify the range without adjusting anything else as well...

[vba]Range("A:B").Printout copies:=1[/vba]

[lucas]

I really went about it the hard way didn't I Zack....!

[mdmackillop]

Hi Lawson,
This is one of these things where it is best to use the macro recorder.
Start the recorder then set a limited print area; go to page setup and change some settings there; finally send your sheet to print. You'll end up with something like this
[vba]
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/12/2006 by Malcolm
'
'
Range("A10:C12").Select
ActiveSheet.PageSetup.PrintArea = "$A$10:$C$12"
With ActiveSheet.PageSetup
.PrintTitleRows = "$10:$10"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$10:$C$12"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
[/vba]

Go through it and get rid of all the lines that are not relevant to the task. You can also add inputboxes for user input eg number of copies.

[vba]
Sub Macro2()
ActiveSheet.PageSetup.PrintArea = "$A$10:$C$12"
With ActiveSheet.PageSetup
.PrintTitleRows = "$10:$10"
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.4)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=Inputbox ("How many copies")
End Sub
[/vba]