We have an excel report that we would like to send to a printer.

Unfortunately, when we select printer output reportcaster tells us to use DOC,WP, or PS output formats.

Ideally, we would like the ability to take this report and make it fit on one page, it currently is printing out about 6 pages in 'portrait' layout, when we need to have landscape layout and 'fit to page' functionality.

Is this possible?

You can't send a report in output excel directly to a printer from within ReportCaster. The printer does not understand that any more than if you sent an MP3 file to the printer. An excel file is program dependent - it is only understood within the excel program and can only be printed from inside of excel. DOC, WP, and PS are program independent and/or presented in language understood natively by a printer (DOC and WP are basically text files with control characters for line feed/carriage return. PS code is understood by any postscript capable printer.) That is also the reason why you can't select PDF as an output format (but PS is a perfect substitute for PDF.)

You could created the report using a template that has a printing/formatting macro embedded, and distribute via email, but you still have to open it in excel to print it.

I haven't made use of any reporting output using excel templates so someone else would have to give you the "how to" on that one. But it is in the documentation, as well as a couple of different presentations available from last year's Summit conference.

Well I didn't know this...but documentation says
The printer distribution option supports only files in PS, DOC, or WP formats.

There must be a way to cheat...

Darin Lee: could you point me to what doc I should be looking for?

I have the reportcaster doc, and the printer output stuff is fairly limited.

As an effort to save face as to why I'm trying to print a spreadsheet from reportcaster...our very inexpensive fax software will take an .xls attachment, open it, convert it to tif and send it on its way.

Creating Reports with Graphical Tools v713 has a section on "Using Excel Templates." As I mentioned, I don't really know much about the whole process. A co-worker attended a session at last year's Summit that presented the whole process. I assume the presentation is available somewhere on the IBI site.

When you say your fax will take an XLS attachment, do you mean an XLS file? Attachment, to me, is something hooked on to an email. And having mentioned that, somehow they have configured our faxes here to be referenceable as an email address, or it can configure an email address to act as a fax client to receive faxes. In which case you could distribute an XLS file directly from ReportCaster as an email and the fax software does its thing. Interesting idea... I'm going to look into that one. What is the software that you use?

Jason,

Just a quick off the head idea, but how about HOLDing it and then using CMD COPY filename.extn \\printserver\printer or something like that?

I have some VBScript at work that I use to open Excel and print sheets. You could set that up in scheduler in Windoze.

T

This message has been edited. Last edited by: Tony A,

Darin Lee: An outfit called FaxStar created this for us. We just send an email to an IMCEAFAX-18005551212@ourdomain.com and a connector sends it over to the fax server, which opens each attachment, converts it to tif and sends it off to 18005551212. The problem is that we had to pay for an extra license of excel for the fax server in order for the software to open the .xls's.

Tony A: I'll try that out. Does the document have to be in postscript or PCL for it to work? are you using something like ghostscript on your windows machine?

Jason,

The output is any Excel type you want. If you can print it from within Excel then you can print it via this VBScript.

The reason I created it in the first place was that a process that I wrote (saving about 20 man hours for the same in hand-draulic methods) create over one hundred compound Excel workbooks (through templates with VBA) via RC. These are all stored on a network drive somewhere. When I asked the user what they did with them, I was informed that they have one guy going through and printing three of the five worksheets in about 50% of the workbooks. Well, you can work out the rest.

The concept is simple, it just mimics exactly what you do manually. Open an Excel app, select each worksheet you want to print and click the print button. The print goes to the default printer on the machine on which the VBScript is executed. -

Dim xlApp
Dim xlBook
Dim xlSheet
Set xlApp   = CreateObject("Excel.Application")
Set xlBook  = xlApp.Workbooks.Open("\\server\\full path to your Excel File\filename.xls")
Set xlSheet = xlApp.ActiveWorkBook.WorkSheets("Sheet1")
xlSheet.Activate
xlApp.ActiveSheet.PrintOut
Set xlSheet = xlApp.ActiveWorkBook.WorkSheets("Sheet2")
xlSheet.Activate
xlApp.ActiveSheet.PrintOut
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook  = Nothing
Set xlApp   = Nothing

Oh, any savings your Co. makes then 10% would be nice and in todays world with the InterWeb, remote consultancy is always a possibility

T

This message has been edited. Last edited by: Tony A,

Oops, forgot to mention this - One thing to remember is that this can be run on any machine with a copy of Excel on it so it doesn't even have to be your server. So you can save on having to get a server version of Excel as well.

T

so, what you did in order to make this work was create a hold file, then shell a vbscript to print it?

Is anyone using the distribution to printer functionality? I really don't see it being of much value to us since it can only print doc or postscript output. I didn't even see pdf on the list.

quote:

An outfit called FaxStar created this for us. We just send an email to an IMCEAFAX-18005551212@ourdomain.com

Why don't you send it as an email rather than printer?

PDF is not on the list for printer distribution, but we make HEAVY use of this distribution method by changing output to PS and have found the output to be exactly the same as PDF in almost all cases. It does require a PS-capable printer, but most (not all) new business-class printers now can handle a Postscript stream.

Tony A: thanks for the ideas.

I've put together a .net console app to print these spreadsheets for us. An example command line would be something like

print_utility.exe c:\p\flash.xls---\\svr99rvi\P99GO---ALLSHEETS---$1:$3---Prepared For JKelly---0---PORTRAIT---1---1---0

.net Source code goes something like this. consider it open source.
Module Module1

Sub Main()

If Trim(Command$) = "" Then Exit Sub

Dim ArrCmd() As String
Dim x As Int16
ArrCmd = Split(Command$, "---")
'-----arrcmd(0) = filename -----'
'-----arrcmd(1) = printername -----'
'-----arrcmd(2) = sheetname[opt] ALLSHEETS -----'
'-----arrcmd(3) = titlerows $1:$3 ----'
'-----arrcmd(4) = route to header ----'
'-----arrcmd(5) = boolean color=0 b&w=1 ----'
'-----arrcmd(6) = "PORTRAIT"/"LANDSCAPE"
'-----arrcmd(7) = fit to pages wide
'-----arrcmd(8) = fit to pages tall
'-----arrcmd(9) boolean print gridlines=1

'ReDim ArrCmd(9)
'ArrCmd(0) = "c:\p\pulse.xls"
'ArrCmd(1) = "\\svr99rvi\p99go"
'ArrCmd(2) = "01 HC"
'ArrCmd(3) = "$1:$2"
'ArrCmd(4) = "ROUTE_TO_JASON"
'ArrCmd(5) = "0"
'ArrCmd(6) = "LANDSCAPE"
'ArrCmd(7) = "1"
'ArrCmd(8) = "1"
'ArrCmd(9) = "0"

If UCase(Right(Trim(ArrCmd(0)), 4)) = ".XLS" Then
Dim Xlapp As Microsoft.Office.Interop.Excel.Application
Dim jSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim myOrientConst As Microsoft.Office.Interop.Excel.XlPageOrientation
Xlapp = New Microsoft.Office.Interop.Excel.Application

Xlapp.DisplayAlerts = False
Xlapp.Visible = True
Xlapp.Workbooks.Open(FileName:=ArrCmd(0), UpdateLinks:=3)
For Each jSheet In Xlapp.Worksheets
If UBound(ArrCmd) >= 2 Then
If Trim(jSheet.Name) = Trim(ArrCmd(2)) Or Trim(ArrCmd(2)) = "ALLSHEETS" Then
jSheet.PageSetup.PrintArea = ""
With jSheet.PageSetup

.LeftFooter = "Printed &D - &T"
.LeftMargin = Xlapp.InchesToPoints(0.3)
.RightMargin = Xlapp.InchesToPoints(0.3)
.TopMargin = Xlapp.InchesToPoints(0.6)
.BottomMargin = Xlapp.InchesToPoints(0.6)
.HeaderMargin = Xlapp.InchesToPoints(0.4)
.FooterMargin = Xlapp.InchesToPoints(0.4)

.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Zoom = False
.Draft = False

If UBound(ArrCmd) >= 3 Then .PrintTitleRows = Trim(ArrCmd(3)) 'add to stream
If UBound(ArrCmd) >= 4 Then .LeftHeader = Trim(ArrCmd(4)) 'add to stream
If UBound(ArrCmd) >= 5 Then .BlackAndWhite = CBool(ArrCmd(5)) 'add to cmd stream
If UBound(ArrCmd) >= 6 Then
Select Case UCase(ArrCmd(6))
Case "PORTRAIT"
jSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait
Case "LANDSCAPE"
jSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape
End Select
End If
If UBound(ArrCmd) >= 7 Then
If IsNumeric(ArrCmd(7)) Then .FitToPagesWide = CInt(ArrCmd(7))
End If
If UBound(ArrCmd) >= 8 Then
If IsNumeric(ArrCmd(8)) Then .FitToPagesTall = CInt(ArrCmd(8))
End If
If UBound(ArrCmd) >= 9 Then .PrintGridlines = CBool(ArrCmd(9))

End With

jSheet.PrintOut(Copies:=1, ActivePrinter:=Trim(ArrCmd(1)), Collate:=True)

End If
End If

Next

Do While x <= Xlapp.Windows.Count
Xlapp.ActiveWindow.Close()
x = x + 1
Loop
Xlapp.Quit()
Xlapp = Nothing

End If '//UCase(Right(Trim(ArrCmd(0)), 4)) = ".XLS"


End Sub


End Module