Solved

Mail Merge, Matrixes, And Automation MAX points!!

Posted on 2003-11-03
8
285 Views
Last Modified: 2010-04-01
This is not necessarily a C++ question although I would like to design the system in C++ if possible but I am getting a few headaches.

Take a Deep Breath you will need it.

I am developing a system so that I can implement a pre-delivery, delivery time letter. This is so the customer knows what time to be in i.e. Am/Pm when their delivery arrives, on a given date.

The current system involves sorting throguh a batch of physical letters. This is how it works

1. Finds the date of delivery on the standard format letter.
2. Find the corresponding day for that date.
3. Look up the postcode Prefix on the letter.

4. Search paper based Matrix to find the row of the corresponding Postcode
5. Read accross the column header which lists the days and find what time the delivery will be delivered i.e. Am/Pm/Both/Neither.
6. Then Highlight either Am/Pm on the letter.


What I wanted to do was semi automate this system were a program can:

1.When Mail merge is implemented save all letters to temp directory  
2.Fseek/Ftell To find the date of deliverySearch the day for corresponding date.
3.Have 5 txt files with matrix information Mon-Fri
4.Open relevant txt file with corresponding day
5.String Search matching postcode from the matrix txt files
6.Fseek AM or PM or Non
7.Store AM or PM as delivery time String
8. Open letter for amendment Fseek position for delivery time
9. Insert relevant time period i.e. Am/PM/Both/Neither
10. Close file


The fault I see with the automated system is that when a mail merge is done all the individual letters are saved under the same file name. Which adds confusion. If they were individual Fseek/Fteel would work perfectly because the letters are a standard format and you can set the offset to find you relevant information.

Secondly how would you be able to find the corresponding day for the date, if the date is in the form DD/MM/YY. It would have to be searched and stored as a string because file manipulation is used.

An alternative way of implementing the system.

The reason why i would have to fseek from the end of the letter is because at the start of the letter in *.doc their is a letter head which would have unknown results if read from the start.

Thanks for your time and pateince.

In theory this is possible but the above issues have put me back to square 1

Shaun

0
Comment
Question by:Bazza1982
  • 4
  • 2
8 Comments
 
LVL 11

Expert Comment

by:bcladd
ID: 9675518
My first suggestion is to avoid C++. Why? Because Office Automation (VisualBasic scripting for Office applications) should be able to do what you want. You can write a macro that runs right after the mail merge creates a letter and does exactly what you describe (finds the string with the given date, determines the day of the week (VB has a Date class though I might not have the right name for it here), looks things up in a great big matrix of information (think Excel spreadsheet) and prints the required letter). Hooking the moment of mail merge is the hardest part in my mind because I have never tried to do it and haven't read any of the documentation.

I urge this because the difficulty of reading .doc files is non-trivial, seeking by position is almost always too brittle (what about the boss getting a new secretary who uses 2 initials instead of 3 when she types the letters; modifying the template by that one character changes your whole program and being off by just 1 character, who is going to notice glancing at a stack that looks about right?). You should be searching for text values to replace; VB has reasonable support for regular expressions as well as access to the search and replace functions in the application.

Having said that and urged you (emphatically) to think about a higher-level approach using the tools that are already in place, I would be happy to help you with any particular problems you have with either approach. As I said I am not familiar with mail merge in Word (and I don't have Office installed at all so I can't really learn) but I know just enough VB to be dangerous.

Hope this helps and good luck, -bcl
0
 

Author Comment

by:Bazza1982
ID: 9680023
Hi Bcladd,

Firstly thankyou for your Reply.

I understand why you would urge me to use VB with office scripting tools although I am unaware of these. I touched on VB once about 4 years ago but my knowlefe is very cloudy. i.e. Dim num (Whatever that means)

First of all im unsure of any commands  to search for the date.
Would the date be searched as a string on the letter and will it support UK date forma, to extract the relevant day ie. Mon-Fri

Secondly how would you write a script to search an excel spreadsheet matrix and could i have one excel file with columns going Mon-Fri and rows being relevant postcodes.

Then I would need to copy the information in that particular cell copied and transfered to the letter which will be amended. So as it needs to replace a word. I presum you could search for  your & delivery & is & on and VB strcat equivalent it with that and the information in the cell.

I think my main problem with this is A. Startin, and B. Coding.

I will aquire a VB ASAP from the library any recommendations or WWW web link would be appreciated relevant to the above.


Thank you for your interest.
0
 
LVL 11

Expert Comment

by:bcladd
ID: 9680426
I  am at work so I don't have time to address your post in detail but:

   In Word (Word 2002 SP-2) you can open Tools | Macro | Visual Basic Editor
   In the editor select Help | Microsoft Visual Basic Help
   In the Answer Wizard, type "day of week"; this will bring back results including the Weekday function (can convert from a date to an integer representing the day of the week).

You can also look under
   Contents | Visual Basic Conceptual Topics | Working Across Applications - for an introduction to opening another Office application programatically (you can open the Excel spreadsheed from within your script so that you can extract the delivery schedule (using an Excel formula you could do it in a couple of operations: write the day of the week in a known cell and read value from a known cell where the second cell uses the data functions in Excel to extract a matching value from the table given the information in the written cell)).

I tend to learn the parts of VB I need by reading the documentation or going on line to MSDN (in the VB editor Help | MSDN on the Web).

Hope this helps, -bcl
0
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!

 
LVL 11

Accepted Solution

by:
bcladd earned 500 total points
ID: 9690896
I was surfing today, thinking a little bit about your problem and I found some tutorials on getting started with VB in Office. Some are a little bit stale but they contain some good pointers:

http://www.vb-helper.com/index_categories.html - some help with visual basic in general. Some choice snippets include interacting with Excel (writing a spreadsheet: http://www.vb-helper.com/howto_excel_write.html; loading a CSV file into Excel: http://www.vb-helper.com/howto_csv_to_excel2.html) and interacting with Word (spell checking programatically: http://www.vb-helper.com/howto_word_spell.html)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffdev/html/vsofficedev.asp - One of many pages at MSDN that discuss Office product automation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd10/html/woevtMailMergeBeforeMerge.asp describes the BeforMerge event and how to hook it (there are several other events including AfterMerge and AfterRecordMerge that you could hook for your use).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtoreplacetext.asp describes the concepts behind find (and find and replace) from VB inside an Office application.

Not all of these resources refer to the most current version of Office; I am pretty sure most of the interfaces remain unchanged. If not I am sure a search of MSDN would reveal the newer interfaces.

Hope this helps, -bcl
0
 

Author Comment

by:Bazza1982
ID: 9717178
Thanks BCL unfortunatley I wont be able to read these till the weekend but thanls for your help and interested it is most appreciated I may stay in touch to let you  know of my progress and bother you if thats ok


cheers

shaun
0
 
LVL 11

Expert Comment

by:bcladd
ID: 9891983
How goes the automation project? Did any of the literature help?
0
 
LVL 9

Expert Comment

by:tinchos
ID: 10242486
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: bcladd {http:#9690896}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Tinchos
EE Cleanup Volunteer
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

680 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