Solved

Macro to copy specific excel cell data into a specific table cell in a word doc

Posted on 2013-01-03
17
687 Views
Last Modified: 2013-01-05
I have a excel workbook (see tenderinfo.xls attached) which contains the 'data', After entering a whole series of data into the workbook, the user needs to click a button to open and complete a word document (see worddoc.doc atttached) with data from specified excel cells.  
EG tenderinfo.xls cell B4 info needs to be copied to the cell next to region on the worddoc.doc.
For info this needs to be done this way for many intercompany reasons, so unfortunately 'why do it this way' is not an option :)
tenderinfo.xls
worddoc.doc
0
Comment
Question by:grayderek
  • 9
  • 8
17 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38741573
Question
1) You want the macro to reside in the Excel file or in the word file ?
2) Will the name of the word file always be the same ?
gowflow
0
 
LVL 2

Author Comment

by:grayderek
ID: 38741984
gowflow
Answer
1) macro to reside in the Excel file
2) Yes
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38742000
ok I'm fine with Excel but not too familiar with word if ur patient I'm willing to look at how to get it to word as see you hv in word rectangles ? is this drawing shapes ? if you tell me how you got these rectangle you will facilitate my life. If I'm slow then for sure someone else will get you the solution. In EE your never at loss !!! :)
gowflow
0
 
LVL 2

Author Comment

by:grayderek
ID: 38742109
gowflow
The rectangles you see is because show/hide paragraph marks is switched on, you can turn it off (attached file has this turned off)
Derek
worddoc.doc
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38742266
ok I think I got it. Try this file make sure you activate macroes and also make sure the worddocument is in the same folder of the excel macro.

Try it and let me know.
gowflow
tenderinfo.xls
0
 
LVL 2

Author Comment

by:grayderek
ID: 38743312
gowflow/
Thankyou so much, I have tested and works except if you press the button again it hangs because does not seem to close down completely (unable to open file because pc thinks it is still in use?).  
However this is file is great and so.so.. close to what I need, it does too much.  
What I would like is for the file opened (worddoc.doc) to stay open once populated to allow the user to save it to their own area, email etc (this might resolve the bug anyway).  This is because in the user will open the file from our intranet, so not be allowed to save back, also ensures they always use the most uptodate version :)  
Look forward to closing this and awarding points.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38743368
well thought of this but when you said the file name is always the same then I thought you must know what your doing.

Here is my suggestion: I can keep the form intact like worddoc.doc will always be as the file you attached and then I can create the new file and gove it the name workdoc & date (the actual date time)  and I will show you the doc created so the user can save it where he wants (if you want we can sav eit automatically on the user's desktop and not where it was read from)

you tell me what suits you.

By the way I forgot to tell you that you needed to add the library of word but if it worked for you then obviously you added up.

Will wait for your reply.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38743405
Sorry for the bug. Pls try this version:

It will create in the same directory a worddoc file stamped with the date and time. It will not show the file to the user but it will tell him that it was saved.

gowflow
tenderinfo.xls
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:grayderek
ID: 38743629
Gowflow: to answer your 2 responses posted:
11:16 (sorry we may have misunderstood each other) Yes the file name it opens will always be the same because it is in a central location (eg intranet or network drive, so would need to code to open file from an amendable location like "G:\TWB2013\WordDoc.Doc")  
Whilst it is good idea, I do not need any autosave/save function as the user needs to preview the info in the file and quite often will close and not save changes. I already have this working in the masterworkbook to open and populate an excel form (no changing the word form to excel is not an option :)  I can sent you my masterfile.
11:37 Re-bug.. ger
This is worse now after first use get 'File in use' error and now will not go away until rebooted?? tested on another pc same prob :(  

Regards and thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38743765
PLs post your masterfile as I hv hard time understanding your problem. it works fine here.
Will wait for your file.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38744248
Still waiting for your file as no prblem fm this side. Maybe the issue is with permission on the network mybe the user does not hv full control on the server in that directory and as the macro open/modify/save then maybe it is going into coma !!!

Hv you tried the workbook on your pc locally ? does it produce the error or problem ? When it comes to networks we need to isoolate issues.

gowflow
0
 
LVL 2

Author Comment

by:grayderek
ID: 38744528
gowflow/  Yes it has all been tested on a local PC's, flash drive and copied to another PC, I have not tried use it on the network yet because of the issue.  
Anyway I received another file from separate source which answered part of my issue but not in the simple way yours did but the other file did not produce 'file in use errors'.  Two of us compared the code stripped everything out of yours that did not seem to be needed and combined the things I needed and it works - I have uploaded this version and the test worddoc  for your and other users info.  If you agree with this I am happy to award points and close with thanks, because without your help I/we would not have got there, a problem shared :)))
tenderinfotf.xls
WordDoc.Doc
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38744577
Well here is the problem I presumed you aded the library to Word but seems you did not whcih caused the problem. Do this on my orginal file and then test it and you should not have any problem (I hope) and let me know.

Open the Excel I posted the last version for your reference I just attched it here.
Goto visual basic and on the Tools menu select References then go down in the list till you find
Microsoft Word 12 or 14 or .. and click on it and press ok save the file and start it again and see if you get the error.

Your file you posted show that it is missing the word library reason for this error.
gowflow
tenderinfo.xls
0
 
LVL 2

Author Comment

by:grayderek
ID: 38747255
gowflow/ You were right with the reason for error I had not realised I needed to add reference in word and excel files.  Tested you file and it works fine.  
However as I said in my earlier comment I do not need any autosave/save function as the user needs to preview the info in the file and quite often will close and not save changes, so as to add data in masterfile and re-preview before saving to our document management system for distribution.  
So we will be using the file created from your code altered to give us exactly what we need.  
I will be closing this question and awarding points to you with grateful thanks.  
Have also attached your and our amended excel files because someone may want to use either plus the worddoc.doc that gets created.
Thanks for all your help, I would not have solved my problem without your help.
Regards Derek
tenderinfo.xls
tenderinfotf.xls
WordDoc.Doc
0
 
LVL 2

Author Comment

by:grayderek
ID: 38747273
I've requested that this question be closed as follows:

Accepted answer: 0 points for grayderek's comment #a38747255

for the following reason:

The code was simple and easy to adapt, it gave me the direction to improve the solution further and provide other with more options
0
 
LVL 2

Author Closing Comment

by:grayderek
ID: 38747274
See comment on my response
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38747559
Tks  for your comments and glad I could help.
gowflow
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now