Multiple worksheets to PDF

[a021263]

Dear all,

I'm trying to find a solution for a problem. I have lots of excel files with lots of worksheets in it (with data and very complex graphs of course). The data in the worksheets are sas generated output.

I have to get a couple of worksheets from every excel file into a PDF file and automate this process.

What have I done already? I have an excel file with all the info like the name of the excel file, name of the worksheet and where it has to save the worksheet as a pdf. This is also generated by SAS.

Maybe the following example gives a clearer picture of what I mean.

Test.xls Worksheet1 C:\PDF Generation\Dir1
Test.xls Worksheet4 C:\PDF Generation\Dir2
Test2.xls Worksheet2 C:\PDF Generation\Dir3
Test2.xls Worksheet7 C:\PDF Generation\Dir4

This list goes on and on...

What would be the best/easiest solution to get all the worksheets in pdf files and in the right dir?

Perhaps with some VB code, this would go easier than with SAS? Problem is that I'm not a VB programmer and it has to go quick. If somebody could provide me with VB code that could accomplish this, I would be very grateful!!

ps. I've tried to call a macro of excel in SAS, but you always have the problem that it askes where and what name to save for the PDF.

Kind regards!

 

[jymm]

ok - I have been out of SAS for a while and would do this with SQL SRSS or Crystal - BUT - have you considered using something like Primo Pdf and just 'printing' to that 'printer'?

http://www.primopdf.com/

-or-

http://www.neevia.com/products/dplt/

are just two of the alternatives I have used in the past (depending on licensing and long term needs).

 

[ChrisW75]

SAS also has an ODS PDF destination (V9 does anyway), it's worth checking this out, however it can be pretty tough to get it looking good (I spent about a day here this week trying to get a report looking halfway decent, but in the end I got the corporate logo on the header, and each graph and table on it's own page in the PDF file). There's a fair amount of example in the various SUGI papers on the SAS site, however, it's all fairly disjointed, ODS is fairly poorly documented in the reglar documentation.
Have a stab at it and see how you go, it's worth checking out these papers:-

http://www2.sas.com/proceedings/sugi30/172-30.pdf

http://support.sas.com/techsup/technote/ts659/ts659.pdf

http://www2.sas.com/proceedings/sugi27/p117-27.pdf

If you get stuck, post back here and I'll help out.

 

[ChrisW75]

One thing to watch out for is that tabular data, such as from proc tabulate, is hard to position on the page, it is much easier to place tables each on their own page. I tried to get 2 tables on the right side of a landscape page and the graph they were describing on the left, it didn't work, one table would go to the right, the second would go behind the graph.

 

[a021263]

Thanks ChrisW75 for your help.

My problem lies in the fact that the worksheets that I need in pdf are NOT generated by SAS. Imagine that you have 8 worksheets with data in that are generated by SAS and 3 worksheets with graphs and info that are based on the data of the 8 worksheets.

I've searched and searched for a way to automatically create pdf's of those 3 worksheets. It's the same as if you would open the excel file and go to the printer driver and create a pdf of the opened worksheet and repeat it for the next.

It seems that SAS can only write to pdf IF you have the data of the worksheet in a data set. You can't put the thing I have in my worksheet in a simple data set.

I also tried to make an excel macro and call this macro in SAS, but then you would manual give the dir to save and the name and hit the save button. This should all be automated, so that's no solution either :-(

I really hope someone could give me the perfect solution.

Many thanks in advance!

Kind regards!

 

[ChrisW75]

I'm not much of a VB programmer, I've done some small VBA things, but you're talking about at least half a days work, probably more like 1-2 days work, and I really can't spend that kind of time on this. Sorry.
I can see 3 possible routes here.

1 - Re-write the excel stuff to be in SAS, not sure why you think this can't be done, but I'll take your word for it.
2 - Write some VBA code in your excel spreadsheet to automate printing it to a PDF file.
3 - Use one of the PDF creators mentioned by JYMM, above, to create the PDF file, and set up a command line script which runs the SAS program and then "prints" the Excel spreadsheet to a PDF file. Should be reasonably straightforwards.

 

[jymm]

Or - I do something simlar with a word template that only prints (ok - it actually send to our 'fax' printer so that it I fax pdf / word invoices to the customers)

I run this batch file on a scheduled basis (we use VisualCron - which is an AMAZING piece of software if you can talk your way into getting it on a server - was under $100)

REM Use Word to print the faxes to the fax printer
"C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE" e:\Apps\JimTransfer\AutoPrint.doc /mPrintIt
exit

PrintIt is a macro in Word (so you should be able to do this in Excel - although I have not tried)

Sub PrintIt()
'
' PrintIt Macro
' Macro created 3/9/2005 by Jim 
'

FileDir = "E:\Faxes" 'The directory with the files
ChDir (FileDir)
adoc = Dir("*.DOC")                 'The files you want to print
While adoc <> ""
    Documents.Open FileName:=FileDir + "\" + adoc
    PB = Options.PrintBackground    'Save current printing status
    Options.PrintBackground = False 'Disable background printing
    Application.ActivePrinter = "\\servername\faxprinter" 'Sets the active printer to Fax maker
    ActiveDocument.PrintOut         'Print it!
    Options.PrintBackground = PB    'Restore previous status
    Documents.Close                 'Close it
    adoc = Dir()                    'Get the next filename
Wend
Application.ActivePrinter = "\\servername\default 'Sets the active printer back to the default
Application.Quit
 

End Sub

This should get you started - excel printing with each sheet should not be that difficult to insert into here.

Let me know if you need any help with it - (but I will not be much help for about a week)

 

[jymm]

hey wow - I get to stick around through Wednesday (7/12) so if you have questions - find me here!