PDA

View Full Version : [SOLVED] Printing a series of records



AndyM
03-06-2005, 11:59 AM
Hi All .. hoping for some pointers here.

I have a workbook that comprises of two principal sheets.

The first is a transaction list of people and various numeric measures, the second is a formatted sheet that analyses the transaction list and produces an output report containing various charts.

At the moment, I manually select the record that I want to print and then I print the report page. I repeat this for each record in the list. Rather than printing to printer I am 'printing' to PDF via Adobe Acrobat. Each time I create a report I set the file name to be a combination of the person's name and an element of the unique transaction id that is held in the transaction file.

What I would like to be able to do, is to automate the printing of a series of reports.

Ideally I would like to set a start and finish transaction number for the transaction range (they are stored sequentially from 1 to n) and for the code to walk from the first to the last, passing the transaction reference to the report sheet (which actions various lookups) and for the output file to be created in a nominated directory with an automatic name, comprising of the Trans ref and the name of the person that the report if for (both held in the transaction list).

I hope that this makes some sense, can anyone give me some pointers as to how best to go about this?

Thanks again in advance

Andy

mdmackillop
03-06-2005, 01:59 PM
Hi Andy, can you sanitise a copy of the book to remove and sensitive info and post it as a zip file (use the Go Adavanced button to do this). That will give a better idea of what you're after

AndyM
03-07-2005, 04:34 PM
MdMack

Thanks for the suggestion, hopefully the attached will help me to explain what I am looking for help with.

The workbook has two main sheets
1. Transactions - weekly transactions that are sent over from an OLTP (oops showing my age - meant ERP) system
2. Report - analysis of the individual transactions

Currently I import the transactions then by selecting a record at a time and hitting the print button I produce a print file (pdf).

Ideally, I would like to set a start record # and then automagically produce a pdf output file for each transaction from start to end.

Any pointers/help would be much appreciated.

Thanks for any help

Andy

mdmackillop
03-07-2005, 05:00 PM
Hi Andy,
For a starter, Select your records then run the following macro, (PDFPrinter adjusted to suit):


Sub PrintRecords()
For Each cel In Selection
Sheets("Transactions").Range("B4").Formula = cel
Sheets("Report").Select
Application.ActivePrinter = "PDF995 on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"PDF995 on Ne02:", Collate:=True
Next
End Sub


No more time just now, but I'll look into the file naming tomorrow(unless someone beats me to it). Who's PDF printer are you using?
MD

AndyM
03-21-2005, 02:10 PM
MD

Belated thanks for this - I have been away - hence the tardy response.

The code you suggested worked fine and creates a pdf directly, one last thing I am still struggling with is the naming of files at runtime. As this stands it creates the same file name to the same location each time I run it.

Have you any ideas how I may introduce a 'prompt for filename' element that asks me each time I run this?

Hope you can help, and thanks again for your help so far, it is much appreciated

Andy

mdmackillop
03-21-2005, 03:00 PM
Hi Andy,
The PDF printer I use (PDF995) has a companion product PDFEdit. This basically writes an ini file which is used to either autoname or samename the output as it is created. This can be manipulated from Word/Excel as a textstream file; although I've not done much work on this aspect. I assume others work in much the same way (other than full blown Acrobat). What PDF programme are you using?

I see from rereading the question you have Adobe Acrobat. Sorry I don't know how this programme handles its naming.

Here's one way to try. It's a bit kludgy though. It depends upon your PDF using the ActiveWorkbook name as the PDF file name. I can't test it properly here as I don't have a full copy of the PDF programme.



Private Sub CommandButton1_Click()
PrintRecords
End Sub

Sub PrintRecords()
TmpFolder = "C:\Atemp"
Application.ScreenUpdating = False
For Each cel In Selection
Sheets("Transactions").Range("B4").Formula = cel
Sheets("Report").Select
Sheets("Report").Copy
ChDir TmpFolder
ActiveWorkbook.SaveAs Filename:=TmpFolder & "\" & cel & ".xls"
Application.ActivePrinter = "PDF995 on Ne01:"
ActiveWindow.SelectedSheets.PrintOut
ActiveWorkbook.Close
Kill TmpFolder & "\" & cel & ".xls"
Next
Application.ScreenUpdating = True
End Sub

AndyM
03-21-2005, 04:44 PM
MD

Thanks for this - I will give it a whirl and keep you posted as to how I get on.

I will check out PDF995 as I have read many posts on various boards that seem to rate this quite highly.

As always, your help is much appreciated
Andy

AndyM
03-25-2005, 05:08 AM
:clap:

MD

Downloaded PDF995 and with a couple of tweaks your suggested code works just fine - thanks very much!!

Happy Easter

Andy

mdmackillop
03-25-2005, 10:52 AM
Hi Andy,
I was trying to set up PDF8995 with some fiddly print routines in Excel, and eventually came up with the following for setting the PDFEdit for combined printing, then unsetting it. Here's the code, which may save you a bit of typing.


Sub PrintPDF()
Dim St, Tmp As String
On Error Resume Next
Kill "c:\pdf995\temp.ps"
WritePDFini
PDFrintFinStat ' My print routine
St = Timer ' Autolaunch was tempremental, so I tried this instead
Do
DoEvents
Loop Until Timer - 5 > St
Tmp = ThisWorkbook.Path & "\valbook.pdf"
ActiveWorkbook.FollowHyperlink ThisWorkbook.Path & "\valbook.pdf"
'ClearWritePDFini 'Some problems with running this here
End Sub

Sub WritePDFini()
Set fs = CreateObject("Scripting.FileSystemObject")
Kill "c:\pdf995\res\pdf995.ini"
Set a = fs.CreateTextFile("c:\pdf995\res\pdf995.ini", True)
a.WriteLine ("[Parameters]")
a.WriteLine ("Quiet = 0")
a.WriteLine ("Use GPL Ghostcript=1")
a.WriteLine ("Document Name = Valbook.xls")
a.WriteLine ("")
a.WriteLine ("Initial Dir=" & ActiveWorkbook.Path)
a.WriteLine ("")
a.WriteLine ("Size=a4 8.3 11.7")
a.WriteLine ("Autolaunch = 0")
a.WriteLine ("Output File=SAMEASDOCUMENT")
a.WriteLine ("Output Folder=" & ActiveWorkbook.Path)
a.WriteLine ("User File=" & ActiveWorkbook.Path & "\Valbook.pdf")
a.WriteLine ("Launch=" & ActiveWorkbook.Path & "\Valbook.pdf")
a.WriteLine ("Use Word Buttons=1")
a.WriteLine ("Combine Documents = 1")
a.WriteLine ("Combine Last = 1")
a.WriteLine ("Combine Last Preference=1")
a.WriteLine ("Accept OmniFormat EULA=1")
a.WriteLine ("Autostamp = 0")
a.WriteLine ("[Debug]")
a.WriteLine ("EndDocPort1 = 1")
a.WriteLine ("EndDocPort2 = 1")
a.WriteLine ("EndDocPort3 = 1")
a.WriteLine ("[OmniFormat]")
a.WriteLine ("Accept EULA = 1")
a.Close
End Sub

Sub ClearWritePDFini()
Set fs = CreateObject("Scripting.FileSystemObject")
Kill "c:\pdf995\res\pdf995.ini"
Kill "c:\pdf995\temp.ps"
Set a = fs.CreateTextFile("c:\pdf995\res\pdf995.ini", True)
a.WriteLine ("[Parameters]")
a.WriteLine ("Quiet = 0")
a.WriteLine ("Use GPL Ghostcript=1")
a.WriteLine ("Document Name = ")
a.WriteLine ("")
a.WriteLine ("Initial Dir=" & ActiveWorkbook.Path)
a.WriteLine ("")
a.WriteLine ("Size=a4 8.3 11.7")
a.WriteLine ("Autolaunch = 0")
a.WriteLine ("Output File=")
a.WriteLine ("Output Folder=C:\pdf995\output")
a.WriteLine ("User File=")
a.WriteLine ("Launch=")
a.WriteLine ("Use Word Buttons=1")
a.WriteLine ("Combine Documents = 0")
a.WriteLine ("Combine Last = 0")
a.WriteLine ("Combine Last Preference=0")
a.WriteLine ("Accept OmniFormat EULA=1")
a.WriteLine ("Autostamp = 0")
a.WriteLine ("[Debug]")
a.WriteLine ("EndDocPort1 = 1")
a.WriteLine ("EndDocPort2 = 1")
a.WriteLine ("EndDocPort3 = 1")
a.WriteLine ("[OmniFormat]")
a.WriteLine ("Accept EULA = 1")
a.Close
End Sub