Solved

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

Posted on 2013-01-03
17
734 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 30

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 30

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 30

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 30

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 30

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
 
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 30

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 30

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 30

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 30

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

729 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