Copy & Paste Word doc sheets into Excel sheet (How to??)

deve8ore

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

kadghar

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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 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.

deve8ore

Thank you for your response!

I see what you mean regarding the writing the Word VBA and then the Excel VBA. This is a tool I'm trying to make for our entire department, and the Word docs will be different each time everyone gets one. In other words, the users of this tool will have no idea as to how to manipulate the code.

Is there any way I can code everything in Excel VBA, so when the user selects any Word doc it transfers to the appropriate Excel sheet (and prints as seen in Word)?

Thank you again for your help!

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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.

kadghar

Thank you for your response!

I see what you mean regarding the writing the Word VBA and then the Excel VBA. This is a tool I'm trying to make for our entire department, and the Word docs will be different each time everyone gets one. In other words, the users of this tool will have no idea as to how to manipulate the code.

Is there any way I can code everything in Excel VBA, so when the user selects any Word doc it transfers to the appropriate Excel sheet (and prints as seen in Word)?

Thank you again for your help!

Matt

Yes, its possible, in your Excel's VBA you can open a Word instance with something like this:

Expand|Select|Wrap|Line Numbers

 dim Obj1 as object

set obj1=createobject("word.application")

obj1.visible=true

yeah, i know its the same we did before.
To open the file you want i'd recomend you to use the Excel's File Dialogs, chose a file there and open it with the word instance. that's quite easy too:

Expand|Select|Wrap|Line Numbers

 with application.filedialog(3) 'actually the parameters are office constants, but i rather use their numeric value, 3 is for the file picker.

.show

obj1.documents.open (.selecteditems(1))

end with

its a good idea to put something like if .selecteditems.count = 0 then "hey select someting", you can also play with the filedialog's filters.

HTH

deve8ore

Sorry to bother you again, but do you know how to do something comparable with a PDF file?

Our department is using Adobe 8.0 reader, and I can copy / paste ONE sheet as a picture into and Excel sheet at a time. Some files have 20+ sheets, I'd like to copy / paste the PDF as pictures (exactly how they appear in Adobe) into Excel as pictures.... all 20+ pictures.

It seems that if one sheet can be copied and pasted, there should be a way to select all sheets and paste. When I select all sheets, it still only copies one (the first) sheet into Excel.

This would be a HUGE help.

Thank you again,
Matt

kadghar

Sorry to bother you again, but do you know how to do something comparable with a PDF file?

Our department is using Adobe 8.0 reader, and I can copy / paste ONE sheet as a picture into and Excel sheet at a time. Some files have 20+ sheets, I'd like to copy / paste the PDF as pictures (exactly how they appear in Adobe) into Excel as pictures.... all 20+ pictures.

It seems that if one sheet can be copied and pasted, there should be a way to select all sheets and paste. When I select all sheets, it still only copies one (the first) sheet into Excel.

This would be a HUGE help.

Thank you again,
Matt

Well, actually i dont know, may be using shell command, and sendkey to copy paste might help. But im afraid i dont know how to connect with any adobe application.

Anyway, i'll google it, in case i find something i'll let you know.

Copy & Paste Word doc sheets into Excel sheet (How to??)

Post your reply

Similar topics