472,790 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,790 software developers and data experts.

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

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
5 23432
kadghar
1,295 Expert 1GB
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
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
1,295 Expert 1GB
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
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
1,295 Expert 1GB
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

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

Similar topics

8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
4
by: Hitesh | last post by:
Hi, I have three datagrid control on my aspx page and one export to excel button, i want to export all the 3 datagrids contents in one excel file. how can i achive that? -- Thanks Hitesh
3
by: Mike Caputo | last post by:
I'm setting up an auto e-mailer in VB.NET to send daily reports to clients. I have the main part of that all set, but there are a couple of things I'm not quite sure on: 1) I'll be sending about...
0
by: Vader | last post by:
I am new to this forum. Thanks in advance for and help. The following is what I am looking for: 1. I need help with VB code to open a MS Word (.doc) file. 2. Read lines from the MS Word (.doc)...
1
by: dom_perron | last post by:
Hi, I have a problem when I want to paste data from a workbook to another in excel using the clipboard. Each time a try to paste the data in the active sheet I got an exception "Microsoft Office...
0
by: dev121 | last post by:
Hi, I am currently trying to copy a single selected item from a listbox control in vba (excel). So far I have got this code, which doesn't work. <code> Private Sub btnAddNewOrder_Click()
4
by: Harshe | last post by:
hello all, I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with. In a workbook at the start of the year (january) i will have 4...
3
by: samj | last post by:
Looking at code examples, it would seem this is simple, but I can't figure it out. From an Access 2000 form command button, I want to copy all worksheets in an existing workbook to an existing...
2
by: deve8ore | last post by:
Hello, I'm working with Adobe Reader 8.0, working on a project for work, so not allowed to download any other software/ freeware. We receive PDF files frequently with about 20 sheets. I'd like...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.