Transfer Excel to Word

[Robin Clay]

Greetings !

I wrote in in microsoft.public.excel.programming,

-----------[ 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.

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

Ed responded to my cry in
microsoft.public.excel.programming,
2003-10-15 14:13:43 PST

Hi, Robin. I'm by no means an expert, but I do a lot of
the cut-and-paste
you're talking about, though not on that big of a scale.
You don't need to
write the macro in Excel, but in Word - so you may want to
take this over to
a Word newsgroup (microsoft.public.word.vba.beginners is a
good one).

Really, though, all you need to do is walk through it in
Word with the macro
recorder on. Here's what I did for my stuff:

Select the Excel range and copy. Go to Word. Turn on the
macro recorder.
Select File>Page Setup Set your document size and margins.
Select Edit>Paste Special>RTF. This gives you a table in
Word that is
easier to format.
Select Table>Select>Entire Table
Here you can set your font and paragraph formatting.
Also, by selecting
Table>Table Properties, you can set attributes for the
entire table.
On the Columns tab, you can set individual column widths.
On the Rows tab, you can set a specfiic row height or
AutoFit. Also, if you
have any rows that are more than one line high, I
recommend selecting the
entire table, then unchecking Allow Row to Break Across
Page.
One of the last things is to select your top three rows,
then go to
Table>Table Properties>Rows and check Repeat as Header.
Don't forget to turn the recorder off.
If you save with a certain naming convention
(like "C:\ThisFolder\ThisFile
on today's date.doc"), that is easily added into the code
after you record
everything.

Good luck.

To which I responded -

I do a lot of the cut-and-paste you're talking about,

though not on that big of a scale.

45 pages. Yes, that, I fear may be the big challenge....

You don't need to write the macro in Excel, but in Word

Well. I had thought that, by using both, I could break
the operation into small bits, that would not crash WORD -
for I wrote in here, 10/15/2003 5:54:10 AM:

- so you may want to take this over to
a Word newsgroup

Thanks, maybe I go there later.

walk through it in Word with the macro recorder on.

I'll try that.

Here's what I did for my stuff:

Thank you for your suggestions - I'll give it a whirl.

Good luck.

Thanks ! I guess I may need it...

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

So I've TRIED Macros in WORD, but without much success.

WORD crashes whenever I try to paste (Ctrl V) the data -
currently 18 Columns by 2644 Rows, including 3 rows of
Heading.

However, both WordPad and NotePad accept Ctrl V without
blinking.
I CAN then save the WordPad version and then open it in
WORD...
but that's a hassle !

If I try "Paste Special" as an RTF,
then the data loads into the WORD document OK -
but it then has to be formatted !

What I did before (manually) was to
open an Excel "window" in WORD,
adjust it for the right number of rows/Columns,
exit back to WORD,
and then select the "object"
and re-size it to suit the margins.

If I try to record this in a Macro,
having exited from the Excel Window,
I cannot then Select the Object by clicking on it,
I have to backspace into it; nor can I adjust the size of
it.

With the RTF Paste, I would have to
adjust the font sizes and the column widths individually
to fit.

AND Merged cells don't stay merged...


Is there any way that I can interrogate the ClipBoard
from WORD to find out how many Rows / Columns there are,
so that I could put that information into the macro ?


Any advice would be very welcome !

RClay AT haswell DOT com

 

[Helmut Weber]

Hi Robin,
sorry for not presenting a solution to your problem,
but I suspect, tables holding 2700 rows might be too
much for word anyway. But as long as no Excel cell
contains a line break, it should be possible to avoid
tables at all and convert the Excel data to plain
text separated by tabs.
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, NT4.0