Solved

Mail Merge, Matrixes, And Automation MAX points!!

Posted on 2003-11-03
8
281 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Unlike C#, C++ doesn't have native support for sealing classes (so they cannot be sub-classed). At the cost of a virtual base class pointer it is possible to implement a pseudo sealing mechanism The trick is to virtually inherit from a base class…
Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
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 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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now