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
Solved

Mail Merge, Matrixes, And Automation MAX points!!

Posted on 2003-11-03
8
284 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

856 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