par4724
03-10-2008, 04:26 AM
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.
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.