Copy from Excel to Word error - table alignment

[par4724]

Hi, I'm new to the site and VBA. I have managed to format a worksheet from Excel into a table and then copy it into Word using VBA. Once it is in Word, this is where the problem starts. The table is placed into Word but overlaps on the right hand side of the page. To remedy this, i recorded a macro and then pasted it into my script (this included highlighting the table and then selecting table>table properties>center>setting the preferred width to 18cm). This gave me the macro below but i get an error when the VBA reaches "wdStory" and "CentimetersToPoints(18)" with the error "Compile error: Variable not defined". If i run this in Word as a macro it's fine but when trying to control Word at the end of an Excel macroi get the error. If anybody can help me with this i would be grateful.
Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=6
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.HomeKey Unit:=wdLine
Selection.MoveDown Unit:=wdLine, Count:=32, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=69, Extend:=wdExtend
Selection.MoveUp Unit:=wdLine, Count:=7, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=10, Extend:=wdExtend
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Tables(1).Rows.Alignment = wdAlignRowCenter
Selection.Tables(1).Rows.LeftIndent = CentimetersToPoints(0)
Selection.Tables(1).PreferredWidthType = wdPreferredWidthPoints
Selection.Tables(1).PreferredWidth = CentimetersToPoints(18)
Selection.HomeKey Unit:=wdStory

Thanks and regards,
Paul.

[Dave]

It sounds like you need to set a reference to the Word object library in XL. Otherwise this code may be of some use. Save and close your doc. after placing the table and then run this code (no Word reference required). Good luck. Dave
[VBA]
Sub FitTable
Dim oWDBasic As Object
Set oWDBasic = CreateObject("Word.Application")
oWDBasic.Documents.Open Filename:="D:\yourdocname.doc"
'autofit table cell contents
With oWDBasic.ActiveDocument.Tables(1)
.Columns.AutoFit
End With
oWDBasic.ActiveDocument.Close savechanges:=True
oWDBasic.Quit
Set oWDBasic = Nothing
End Sub
[/VBA]

[par4724]

Hi Dave,

Thanks for your reply, it worked great and i now have a table that fits within the page. Now i will work on the margin size :o). I appreciate your help.

Kind regards,
Paul.

[Dave]

Now i will work on the margin size

Paul I'm not sure what this means?

I have managed to format a worksheet from Excel into a table and then copy it into Word using VBA.

Perhaps it might be better to make/format a table in Word and then add your XL data to it? Anyways, if you have some code to copy and paste an XL table to Word, I'm sure it would be handy someday. More fully describe your margin size difficulties and I'm sure someone will be able to help you out. Dave
ps. that doc. instead of .doc in the previous post is just plain annoying...whoops

[par4724]

Hi Dave,

What i meant by that was that i have the basics now and i just have to configure the margins to the exact size that i want them. When i pasted them the default width was 15.48 and aligned to the left. I would like it to be centered and the width to be 18cm or 125% but i will have a look in the KB and find the answers. Thanks again.

Paul.

[Dave]

Hi Paul. I'm not sure if you have resolved your margin difficulty but this link seems relevant. See Md's post on adding pseudomargins. Seems like it would work for you. Either have the boxes in the document before you paste the table, or add them after you paste (if that's possible) and before you autofit...Or perhaps the boxes could be added then the tables and then the autofit... seems more plausable. Anyways, HTH. Dave
http://vbaexpress.com/forum/showthread.php?t=18353

[par4724]

Hi Dave,

Thanks for your reply. This looks like a nifty way to get margins. I did actually manage to get the margins i wanted by using your suggestion of "AutoFit" and then exploring some more until i got the script below: -

With wdApp.ActiveDocument.Tables(1)
.Columns.AutoFit
.Rows.SetLeftIndent LeftIndent:=-57.6, RulerStyle:=False
.Columns(3).SetWidth ColumnWidth:=153.3, RulerStyle:=False
.Columns(4).SetWidth ColumnWidth:=144, RulerStyle:=False

As it is the same template that will be used with a different Excel sheet every time, this will always work.

Thanks again for your help.

Regards,
Paul.