Project : Maintaining Records and Preventing Duplicates!

I'll explain in brief. My wife works in pharmaceuticals industry. She serves a function in a department called regulatory cell. Sometimes, she discusses some problems at work with me. Here is the case (based on what she told me, I have not seen):

1. There is a particular document which is issued to administrative bodies.

2. They (She & Her Colleagues) prepare a dated Word document for this purpose based on product, customer and admin body to be addressed. The format is fixed for each admin body.

3. The Word copy thus created is stored in a folder for future reference.

4. A log is maintained manually in Excel which shows:
Product (s) | Customer (s) | Customer's Addresses.| Authorities | User | Date

As it was apparent there are (will be) glitches in step 3 and 4. So I suggested they contract a software solutions provider to create a program to handle precisely. I do not know what brewed in between but they got a program at place but it is clumsy behemoth and have to use 2007 office. That set them little back as the user interface is changed.

So when at home, when we discuss sometimes it seems to me:

1. The persons who interpreted the requirements from her side were not exactly knowing what they wanted.

2. The software solutions people misinterpreted some part of it.

So the whole project has gone awry. I want to take it up as self improvement program (since I had suggested it) to improve my programming abilities with VB. There is no "official" pressure and deadline.

I hope to pick up some valuable advice as to how to approach it? Right from choice of application: Word, Excel, Access or combination etc.

Suggestion, from an Excel perspective.
1. Create a template for each Word document
2. Create an Excel userform where log data is entered and a list of templates is available.
3. When a new entry is made, and a template selected, a new Word document is created and saved in the appropriate folder
4. You might want to populate Bookmarks in the document from the Excel data. Is MailMerge appropriate?
5. The document can be opened for further editing, or can be printed/sent by email if no further input is required.
6. Look at BuiltInDocumentProperties. You may also wish to add some data to these fields of the Word Document.
7. You could create a hyperlink to the Word documents in the log sheet for ease of access.

I'll give my option from the Word side.

All of the items mdmackillop recommends is how I manage my documents. Only I keep 'my' list in a Word table. (Which BTW, I don't recommend if the log could get long. Say > 500 rows. Large Word tables can be gremlins. I start a new log file every year.)

I'll give a brief overview of my case file.

I get a copy of a part order along with the reasons for the report. Starting with the log file (the Word table), I call a userform based on the part classification. I fill in the userform with all the info. The userform then creates a document based on the predefined template, again based on part classification. The info from the userform populates formfields on the document. Before the macro finishes, I also have it write that info to the log file. The macro finishes and I can fine tune the document.

You recall I have 'my' list in Word. Wouldn't you know, the program manager called after we started this contract and said he wanted the log file in Excel. So I just wrote more code to fill the Excel log file right after the Word log file. I have 'mine' and he has 'his' log files. 'Why don't you move the log exclusively to Excel?', you ask. Because all I really do in Excel is keep a log file. The macro does many more things in Word. It creates a photo file for visual evidence. It e-mails the doc to the manager that he electronically signs and sends to three other people. It moves the document from draft to final file folders so *I* can keep track of where the document is. Finally, it grabs a document number from the completed document and adds it to the log file. The log *knows* which document is outstanding (color coding) and it creates a 'Outstanding Log' e-mail for my boss so he can keep track of what needs attention.

I'd suggest that you sit down with your wife and write out a flow chart. Talk it through and plan for exceptions/problems.

You might get the dept heads involved to issue a SOP (standard Operating Procedure) to help maintain the log file and document integrity. Our program manager issues the document (with a document number in the footer) stating, 'This is the sample template. Make all documents with this classification look like this.'

Hope this helps.

Thank you. You are always a good help. But I might turn up for some advice in future as well. I'll chalk out:

My own position:
1. I know nothing about Word VBA except that it exists. No pun. I've used Word as a normal user does i.e. for preparing documents and sometimes CV.

2. I know very less about Templates, but have not tried them on my own. Bookmarks I'd read in XLD's posts.

3. MailMerge is now fascinating me as I've come across this term twice in last few days. Someone was using it to generate his macro for some other software.

4.

Quote:

---

Originally Posted by mdmackillop

6. Look at BuiltInDocumentProperties. You may also wish to add some data to these fields of the Word Document.

---

I will look into it.

5. For better part of the last year,I was a tad better than idle. And that helped me work on and get first hand knowledge of VBA (All Excel). Thanks to this forum. You guys have been generous and supportive in knowledge sharing. But now I am leaving for 3 months assignment to Japan so I'll be able to participate less.

I had thought of following: Kindly correct me if something is not possible or can be done in better manner:

1. Creating standard template in MS-Word with specific fields (By fields, I mean some markers that will be able to pick up the entered data).

2. Then when the user saves document (I am supposing that there will be an equivalent document save event), the data from the fields will be picked up and saved into the log sheet.

3. This will be useful from the viewpoint that it will not let the document be generated if it is duplicate.

I might be gone for a while now (after 30th June). But I will come back and keep you posted with updates.

You can record macros in Word as in Excel, and save them in the Template. Once a new document is created, you can run these macros to change the Word document by simply calling them from Excel. This may be simpler that trying to manipulate Word Objects from Excel.

I had posted between your posts #2 and #3, but can't see it. I am struggling to pick up post #3.

Does it mean that
We can call macros in Word while we are in Excel by using:
[VBA]Call MyWordApp[/VBA]

Thank you.

Time is running out for me as I am into the packing mode (for Japan assignment). Will get back to this work as soon as I get back. Could you recommend a good book that covers inter-app aspects (like Excel - Word) which I will go through while in Japan and later use to work. At the moment I have:
Bill Jelen's Excel Macros and VBA
Matthew MacDonald's The missing manual series (MS-Access).

Thank you David,
It looks to me that Word itself can do many things on its own. But to be truthful my slate is still blank.

I sure will sit with my wife before touching the coding part. Got a month here in Tokyo and then I will be back to my favorite pastime: VBA!