Greetings !

I wrote in here, 10/15/2003 5:54:10 AM


-----------[ Previous message ]---------------------
I have a spreadsheet, from which I have to abstract
(from two different WorkSheets) two tables each
extending to about 2,700 rows long by 18 columns
wide, and include them into a WORD document.

Each will therefore run to some 45 pages,
so I want the top three rows to appear
as a heading on every page.

One set will be Appendix A
and the other Appendix B.

What I would like to do is to have a VBA routine
such that I can simply place the cursor in the WORD
document, then Alt-Tab into the Excel WorkBook,
select the entire area that I want to copy,
and then click on a ToolBar Button.

I will be asked how many rows I want as Header,
and the data will be pasted automatically at the
cursor position in the Word Document
in the right number (45) of different bits, one per page.


When I tried an ordinary cut 'n' paste, WORD crashed....
I guess I was trying to paste too much data.

-----------[ End of quote ]--------------------------

....but I omitted to ask the question !

Here is what I have so far -

----------[ Code snippet ]----------

'01. Find out the width to be transferred
For N = StartCol To EndCol
BlockWidth = BlockWidth + Columns(N).ColumnWidth
Next N

'02. Hence establish the scale factor to print on one
page-width
Factor = PageWidth / BlockWidth
PartLength = PageLength / Factor


For N = StartRow To EndRow
'03. Establish the appropriate number of Rows
' to fit on one page-length

'04. Set up block to be transferred,
' = Header Rows plus appropriate number of new rows
' from selected Block

'05. Create a new WorkBook and paste the page-full
' of data into it (values, not formulae);
' Copy the column widths and Row Heights !

'06. Repeat until the entire Block has been dealt
with,
' and stored in separate WorkBooks, one per page,
' each one saved as it is finished.
Next N

'07. Write a Procedure to a "Macro" file,
' to be loaded up by Word
' that WORD will use to load each WorkBook in turn
' into an Excel "window"
' The Code will have to include sizing the window.

--------------[ End of snippet ]----------

a) Is this logic the right approach ?
Is there a better one ?

b) I would much appreciate some help with
this last step, Step 07.

I have not written a macro for WORD before,
so it's all a bit - erm - frightening !

RCLay AT haswell DOT com