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

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 FilePage Setup Set your document size and margins.
Select EditPaste SpecialRTF. This gives you a table in Word that is
easier to format.
Select TableSelectEntire Table
Here you can set your font and paragraph formatting. Also, by selecting
TableTable 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
TableTable PropertiesRows 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.

Ed

"Robin Clay" wrote in message
...
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

Hello, Ed !

You responded to my cry, 2003-10-15 14:13:43 PST

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

.....erm...
so do YOU have a Macro (or more) to do the job ? <grin

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:

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

- 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... :(

Ed responded to my cry, 2003-10-15 14:13:43 PST

for which, many thanks.

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

.....erm... so do YOU have a Macro for it ? <grin

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:

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

- 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... :(

This is the macro I use. It works for me for large chunks of Excel. But it
may not come out the way you like it. Fell free to use and tweak as
required.

Sub PasteTable()
'
' PasteTable Macro
' Macro recorded 6/20/2003 by Authorized User
'
Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
wdInLine, DisplayAsIcon:=False

Selection.Tables(1).Select
Selection.Shading.Texture = wdTextureNone
Selection.Shading.ForegroundPatternColor = wdColorAutomatic
Selection.Shading.BackgroundPatternColor = wdColorAutomatic
Selection.Font.Color = wdColorBlack
Selection.Font.Name = "Times New Roman"
Selection.Font.Name = "Times New Roman"
Selection.Font.Size = 10
WordBasic.TableRowHeight RulerStyle:=0, LeftIndent:="0", Alignment:=0, _
AllowRowSplit:=1, TableDir:=0
Selection.Cells.AutoFit
WordBasic.TableRowHeight RulerStyle:=0, LeftIndent:="0", Alignment:=1, _
AllowRowSplit:=1, TableDir:=0
WordBasic.TableRowHeight RulerStyle:=0, LineSpacingRule:=0, LeftIndent:=
_
"0", Alignment:=1, AllowRowSplit:=1, TableDir:=0

End Sub

Ed

"Robin Clay" wrote in message
...
Ed responded to my cry, 2003-10-15 14:13:43 PST

for which, many thanks.

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

....erm... so do YOU have a Macro for it ? <grin

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:

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

- 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... :(