Transfer data from EXCEL to WORD

[Robin Clay]

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

 

[Ed]

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.

Ed

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

 

[Robin Clay]

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

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

 

[Robin Clay]

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,

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

 

[Ed]

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