Link to home
Start Free TrialLog in
Avatar of grayderek
grayderekFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
Avatar of grayderek

ASKER

gowflow
Answer
1) macro to reside in the Excel file
2) Yes
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
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
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
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.
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
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
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
PLs post your masterfile as I hv hard time understanding your problem. it works fine here.
Will wait for your file.
gowflow
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
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
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
See comment on my response
Tks  for your comments and glad I could help.
gowflow