Hello,
I have a Word document called ([ValidAnalysis]), a named range that will
open up a new Word doc dependent on what a user selects in Excel.
Could someone please guide me on how to set up VBA code to copy and paste ALL sheets in the Word doc and paste into Excel? The sheets normally contain a graph or two, so if it's a copy & paste special as a picture, that's fine.
Any help would be appreciated.
Thank you,
Matt
sure
first of all, to manage an Excel's Sheet from Word's VBA, you'll have to open the excel application as an object, then use the ActiveX controls; use something like this in Word's VBA
- dim Obj1 as object
-
set obj1 = createobject("excel.application")
-
obj1.visible=true
-
obj1.workbooks.open ("c:\mybook.xls")
this will open the book you want to work with, and you can reference it as Obj1
now to copy paste a graph.. you'll have to selecte it as a shape...
something like
- activedocument.shapes("shape 1").copy
-
obj1.worksheets("sheet1").paste
Check the Selection properties in Word, and the Paste and Paste special methosn in Excel's VBA
also have in mind that if the parameter of PasteSpecial is an xl constant (such as xlValues), in Word's VBA you'll have to write it's numeric value instead.
and that to know the shape's name, you might have to record a macro that envolves it.
e.g.
in excel's VBA you can write:
Selection.PasteSpecial Paste:=xlPasteValues
in word's VBA you should use:
Selection.PasteSpecial Paste:=-4163
I hope this might point you in the right direction.
If you have any particular doubts, we'll be glad to help.