Hi VBA developers,

I have an excel spreadsheet with 100 rows. Each row contains details such as Company Reference Number, Company Name, and Revenues for Year 2008, 2009 and 2010 (these are the main headers).

I need to create a Template in Excel (possibly on a second worksheet) displaying this information and link the data to the template so it will loop containing the data of every row. In other words I will have to print the template x 100 (i.e. nr of rows). Ideally I would like to print this as a PDF file (Is this possible by adding VBA coding?).

I believe this will be done by setting arrays and some looping stucture.

If anyone is available to help me please let me know.

Regards

Well my first thought would be to use Word to create a document (template) and link that to Excel with a mail merge, will do what you need. If you are doing it in Excel, I would use the indirect formula to show the data depending upon what row you select. for instance if you have a number between 1 and 100 in cell A1 of worksheet2, to pull out what's in coolumn A you would use:

=INDIRECT("Sheet1!A" & $A$1)

You can easily change the number through code then. As for the PDFs, best way is to get yourself a PDF printer, like CutePDF or similar, preferably one which has an option to not ask for a save location on each file, otherwise you can't fully automate it (you'll sit there clicking OK 100 times). It's easy enough to get VBA to print to the active printer, or even to change printer then print, just record a macro of yourself changing to the PDF printer and printing, use the code it produces. HTH, Dai



------------------------------------------
Do or do not, there is no try. |
------------------------------------------

I tried this software in which enables to convert data file from pdf to excel and extract data from almost any type of PDF. Try this one, it might give you some idea. It works well for me though.