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
5 23487 kadghar 1,295
Recognized Expert Top Contributor
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.Paste Special Paste:=xlPasteV alues
in word's VBA you should use:
Selection.Paste Special Paste:=-4163
I hope this might point you in the right direction.
If you have any particular doubts, we'll be glad to help.
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 - 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.Paste Special Paste:=xlPasteV alues
in word's VBA you should use:
Selection.Paste Special 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 1,295
Recognized Expert Top Contributor
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: - 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: - 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
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 1,295
Recognized Expert Top Contributor
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 the records are sorted by row numbers.
(I had to split the fields to different sheets because Excel has a
limit of 256 fields in each sheet)
My sheets are quite large (~55,000 rows and 200 columns each) and I'll
have to repeat this action many...
|
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
|
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 10-20 reports, and am not quite sure how to queue
them. The reports are anywhere from 500K to 3MB, so I imagine this is
fairly important. Does the SMTPMail object handle this automatically, or do
I need to implement something to wait for each...
|
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) file
3. Save the specific/parsed data on one of the sheets in MS Excel spread sheet.
4. Close up the MS Word (.doc) file.
The VB part is based on the MS Excel spreadsheet file . One of the sheet on spreedsheet will have the path name and file...
|
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 Excel cannot paste
the data." I tried 3 differents method but I always got the
exception.
Here is my code:
| |
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()
|
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 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named...
|
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 workbook and save the results in a new workbook. I keep getting the message "subscript out of range." Any help is greatly appreciated.
Private Sub Copier_Click()
' Copy all sheets in FileA before sheets in FileMaster and save as NewFile
Dim...
|
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 to copy these 20 sheets at one time and just paste them into an Excel spreadsheet (one sheet).
Nothing fancy, no elaborate formatting, I would just like the picture of the PDF sheets in Excel.
I can manually copy one sheet and paste onto the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |