We are an accounting firm and each of our client finacial statements
are in the form of Excel workbooks.  The workbook is typically 10
pages long.  We have developed Word document templates that we wish to
incorporate into the Excel workbooks. So, when printing the Excel
workbook, is it possible to direct, say, page 1 to print using (for
example) the Letterhead Word template, pages 2 through 6 using another
Word template and the remaining pages using a thire Word template.

If not, what is the most expediant means of accomplishing the
forgoing, hopefully without having to paste each page of the Excel
workbook into the document template required for each of hundreds of
clients.

---

Request for Question Clarification by maniac-ga on 26 Oct 2005 19:23 PDT

Hello Jumpercable,

I can think of a few ideas that would meet your needs but am not quite
sure your exact needs.

I assume you keep both the word document templates and spreadsheets in
house / do not send to your customers. If you do send the files to
your customers - are there restrictions on how they are provided?

When you bring the data from Excel into Word now [manually], do you
copy / paste into a single location or do you copy data into specific
areas in the template? In either case, does your template have a
placeholder where the data goes [or would you be willing to add such a
placeholder]?

From what I read, your primary need is to print the reports and not
save them. Would it be beneficial to save the three files (to send to
the customer instead of just printing)?

If you keep the files in house, I suggest using a macro that does
something like this:
 - Open the appropriate Word template
 - copy / paste the data from the "page" (do you mean worksheet?)
 - Print the Word file
 - Close the Word template without saving
and repeats for each following template / page(s) of the spreadsheet.
The macro could be added to a "Macro Workbook" so no changes would be
made to any of your existing spreadsheets.

The complete answer would include the macro to do what is described,
as well as a description of how to create and use the "Macro
Workbook".

Q: Would a solution like this be suitable?
Q: If not, what was not considered for this type of solution?
  [e.g., you need to print all the reports in a folder & want to do
them all at once]

Thanks.
  --Maniac

---

Clarification of Question by jumpercable-ga on 26 Oct 2005 23:27 PDT

Hello Maniac (that has a rather insulting ring to it!)

Yes, we keep both the document templates and Excel files on our
server.  What we will do when have a final financial statement
prepared on Excel is to generally create a draft .pdf file with the
word "Draft" watermarked on each page.  This draft statement just uses
the Excel workbook pages without the Word document template info. that
is incorporated into the final product.

We will the email this draft to the client for their
review/comments/questions and after approval, print off hard copies to
be filed with tax returns, copies for client, copies for banks/other
lenders.  At the moment we print these final copies onto preprinted
Word doc. template forms, but this is awkward, hence the request for
assistance here. The draft sent to clients does not use the template
data, the .pdf file is just on plain background as with any Excel
worksheet.  The final printed form is on Word template created paper.

As to placeholders, we have not as yet used this method - as
mentioned, we load the printer with the preprinted templates and print
onto these forms to form the final product for hard copy distribution.

Our primary need is indeed to print the reports onto selected Word
document templates.  We do not need to store the final report using
templates on our server, as if we need further final hard copies, we
would just redo our present procedures and the plain Excel file on our
server is fine for our internal purposes.

It sounds as if your Macro solution would indeed work - I hope the
foregoing clarification has actually provided some clarification.  To
reiterate what I am hoping to accomplish: - I want to print page 1
(for example) of the Excel workbook onto, say, Word document template
#1, then print pages, say, 2 to 6 of the workbook onto Word doc.
template #2, etc.  It is the printed hard copy that I am trying to
find a way to create more skillfully than what we are now doing.

---

Clarification of Question by jumpercable-ga on 27 Oct 2005 00:04 PDT

Hello again Maniac,

I should add that the Word document templates to which I refer in my
question are nothing fancy - one is our company letterhead upon which
we would print our audit opinion,  another is a page that has our logo
and firm name as a footer to the page and a third template has both a
header and footer incorporating logo, firm name and other information.
 The body of all these templates are blank, awaiting the financial
information from our Excel file.

Hope this helps.

Thanks,

jumepercable

Hello Jumpercable,

What you describe should work pretty well with the macro provided below.

The macro as written makes a few assumptions in the layout of your
information and how you want it printed. I am also including
instructions on how to change the macro if the assumptions are not
correct (or make a clarification request - I will be glad to make the
changes). I also describe where to change the file names for the
template references for your locations (for my tests I used template
files provided by Microsoft).

First, I suggest you create a "Macro Workbook" in the following way:

[1] Use Excel to create an empty workbook.

[2] Use the menu
  Tools -> Macro -> Visual Basic Editor
at this point, Visual Basic should start and display some windows. In
the upper left should be one titled "projects".

[3] In the projects window, select the empty workbook (likely called
Workbook1). Use the menu
  Insert -> Module
a window should appear on the right with a title like Module1.

[4] Copy / Paste the code below into that window. Fix the line breaks
if necessary. Near the top are three lines starting with
  Letter$ = 
  Body$ = 
  Summary$ = 
Replace the strings on each line with the full filename / path to each
of your template files. This will obviously vary with your set up but
look something like
  Letter$ = "D:\Mytemplates\Letterhead.DOT"
where the drive letter is replace by the drive for your templates and
the directory / file name matches the location within that drive.
References to a file on a file server like
  Letter$ = \\myserver\myvolume\Letterhead.DOT"
should work as well.

[5] Use the menu
  Tools -> References ...
and a window will appear. Make sure that the line with Microsoft Word
Object Library is checked and then select OK.

At this point, the macro may need another change. I assumed that pages
2 through 6 (referred to as Sheets(2) through Sheets(7)) are to be in
one file. The same with the last four pages. If this is incorrect,
remove the comments (the ' symbol at the start of each line) so each
page is printed in a separate file.

Use the menu to "Close and Return to Microsoft Excel" and save the worksheet.

To use the macro worksheet, do the following:

[1] Open the macro worksheet. At this point, you may have to say - Yes
I want the macros active (the message varies by Excel version).

[2] Open the worksheet to print out

[3] Use the menu
  Tools -> Macro -> Macros...
and select the PrintWS macro (it will probably look something like
Worksheet1!PrintWS - the file name will be whatever you named the
macro file)

[4] When you hit Run, the macro should run without error. And the last
worksheet will be displayed.
(no modifications to that worksheet)

You can repeat steps 2-4 as many times as needed for each worksheet.

Let me explain the macro as well:

Starting near the top, the macro activates Microsoft Word, and creates
a reference to it called "appWD".

The details for the "Letterhead" step follows:

    appWD.Documents.Add Template:=Letter$ _
        , NewTemplate:=False, DocumentType:=0
Create a new document based on the template specified by the name
assigned to Letter$.

    Sheets(1).Select
Select the first sheet of the workbook (the "first page")

    Selection.CurrentRegion.Select
Expand the selection to the "Current Region". This is the area around
the current cell that is bounded by blank rows / columns. If this is
NOT correct, make a clarification request and describe the range of
cells that need to be selected.

    Selection.Copy
Copy the selection to the clipboard.

    appWD.Selection.Paste
In Microsoft Word, Paste the clipboard to the template file. In the
Microsoft examples, this looks OK, but if it does not work in your
case, make a clarification request and I can suggest some alternative
methods to locate where to paste & paste the data.

    appWD.ActiveDocument.PrintOut
Print the active document in Microsoft Word. There are a number of
options available - let me know if you need them explained.

    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Close the active document in Microsoft Word w/o changes being saved
(does NOT modify the template).

The rest of the code works in a similar manner, modified only to refer
to different pages (sheets) and template files.

If any part of the answer is unclear or you have ANY problems with the
macro or how it works, please make a clarification request. I would be
glad to help you further on this problem.

  --Maniac

PrintWS macro follows
Note - if some of the lines are split into two, be sure to fix those
after copy / pasting the code.

Sub PrintWS()
'
' PrintWS Macro
' Macro created on 10/27/2005 by Maniac
' Prints a worksheet in three sections (letterhead, body, summary)
' 10 pages total (one letterhead, six body, 3 summary)
'

    Letter$ = "Accessory.DOT"
    Body$ = "Bar.DOT"
    Summary$ = "Capsule.DOT"

' Get Microsoft Word active and make a reference to it
    Dim appWD As Word.Application
    Set appWD = GetObject(, "Word.Application")
    appWD.Visible = True
' Do letterhead processing
    appWD.Documents.Add Template:=Letter$ _
        , NewTemplate:=False, DocumentType:=0
    Sheets(1).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
    appWD.ActiveDocument.PrintOut
    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

' Do body processing
' Note:  two alternatives available
'   1 - do five sheets in one file (current implementation)
'   2 - do five sheets separately (remove comments that follow)

    appWD.Documents.Add Template:=Body$ _
        , NewTemplate:=False, DocumentType:=0
    Sheets(2).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Body$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(3).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Body$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(4).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Body$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(5).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Body$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(6).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
    appWD.ActiveDocument.PrintOut
    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

' Do summary processing
' Note:  two alternatives available
'   1 - do four sheets in one file (current implementation)
'   2 - do four sheets separately (remove comments that follow)

    appWD.Documents.Add Template:=Summary$ _
        , NewTemplate:=False, DocumentType:=0
    Sheets(7).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Summary$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(8).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Summary$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(9).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
'    appWD.ActiveDocument.PrintOut
'    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'    appWD.Documents.Add Template:= Summary$ _
'        , NewTemplate:=False, DocumentType:=0
    Sheets(10).Select
    Selection.CurrentRegion.Select
    Selection.Copy
    appWD.Selection.Paste
    appWD.ActiveDocument.PrintOut
    appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

End Sub

---

Request for Answer Clarification by jumpercable-ga on 27 Oct 2005 22:15 PDT

Hi Maniac,

It is after 10 PM Thursday here in Vancouver, so I will look more
closely in the morning and get back to you.  Thank you very much for
your assistance.

Regards,

jumepercable

---

Clarification of Answer by maniac-ga on 28 Oct 2005 14:31 PDT

Hello Jumpercable,

I am glad to help. If you have any problems with the macro, do not
hesitate to ask for a clarification so I can help you further.

  --Maniac