By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,462 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,462 IT Pros & Developers. It's quick & easy.

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

P: 34
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
Jun 5 '08 #1
Share this Question
Share on Google+
5 Replies


kadghar
Expert 100+
P: 1,295
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
  1. dim Obj1 as object
  2. set obj1 = createobject("excel.application")
  3. obj1.visible=true
  4. 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
  1. activedocument.shapes("shape 1").copy
  2. 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.
Jun 5 '08 #2

P: 34
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
  1. dim Obj1 as object
  2. set obj1 = createobject("excel.application")
  3. obj1.visible=true
  4. 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
  1. activedocument.shapes("shape 1").copy
  2. 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.
Jun 9 '08 #3

kadghar
Expert 100+
P: 1,295
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
  1. dim Obj1 as object
  2. set obj1=createobject("word.application")
  3. 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
  1. with application.filedialog(3) 'actually the parameters are office constants, but i rather use their numeric value, 3 is for the file picker.
  2. .show
  3. obj1.documents.open (.selecteditems(1))
  4. 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
Jun 9 '08 #4

P: 34
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
Jun 15 '08 #5

kadghar
Expert 100+
P: 1,295
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.
Jun 17 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.