Automating Word Document Project

[jamescol]

Here is a project I was asked to examine. I have no Word automation skills when it comes to placing text in a document or button controls on a document. So I am hoping to both get some assistance with this while learning at the same time.

The attached file contains a Call Tracking document. It contains some placeholders for a Company's name and contract number, tables to hold contact information, and a table to hold call history activity.

I included the basic requirements at the bottom of the document. I can write the procedures for getting the underlying data, but I need help placing both the controls and data into the proper places in the document.

If anyone can help, I would appreciate it. Suggestions are welcome as well.

Cheers,
James

[JOrzech]

You're getting the information from an Access database then?

[jamescol]

Actually, the data comes from either Active Directory, a SQL data warehouse, or Outlook. Getting the data from these sources is pretty standard for me. Initiating the procedure from the GUI and placing/formatting the data in the document are new to me.

[Tommy]

The macro below is just to show how to color a cell, insert text in a cell , and insert a row.

[VBA]
Sub Macro2()
Dim TblNum As Long
Dim RowNum As Long
Dim ColNum As Long
TblNum = 2
RowNum = 2
ColNum = 1
'set the cells color
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Shading.BackgroundPatternColor = wdColorGreen
'place text in the cell
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = "Hi Ya"
'add a row
ActiveDocument.Tables(TblNum).Rows.Add
End Sub

[/VBA]

I am unclear as to how this is to be used. Are we talking about the user opens the doc, enters/selects a Company and the data is populated? or Are we talking about the data is there and the data needs to be checked? or Is it both?

If the data is there, the Document_Close(), Document_New(), Document_Open() subs could be used depending on what is needed.

On the placement of the Company etc, I would use bookmarks or formfields.
On the Action Item Complete in this cell I would use a checkbox (formfield).

This is just my opinion which is subject to change

[jamescol]

I am unclear as to how this is to be used. Are we talking about the user opens the doc, enters/selects a Company and the data is populated? or Are we talking about the data is there and the data needs to be checked? or Is it both?

Tommy, good questions. Let me explain further. This document is for short-term call tracking when dealing with a customer on a specific topic. Right now, the document is completely manual and the process is cumbersome. Folks either don't use it or make errors by keying incorrect numbers/email addresses, or placing Call History info in the wrong place and not updating the document appropriately.

1. User will create a new document from a Word template
2. When the document first opens, it will automatically determine the user's Active Directory logon name, along with his email address, office phone, mobile phone, and title. In this part of the code, I store the information in the variables:

strADLogon
strADEmail
strADOPhone
strADMPhone
strADTitle

I need to place these values in the first row of the Internal Contacts table.

3. Next, the user needs to click a control (Command Button, ToolBar Item, or Menu Option - My Preference is a Toolbar Item or Command Button) to get the Company information and place it in the document. This first part of the procedure will run a query against the SQL warehouse using the user's logon to filter his customers. I'll present the use a listbox to select the company. That action will provide the Company Name and Contract Number, which the procedure will then place in the proper places in the document.

So let's say the variables I want to place in the document are:
SQLCompanyName
SQLCompanyContractNum

4. At this point, the user may save the document or continue adding information.

5. The next logical step is for the user to add Internal and External contacts to the appropriate table. For this step, I need another control the user can click/select (again, I am thinking a customer toolbar is the best option). This control will pop up a custom Outlook dialog to select contacts. Instead of the traditional To, CC:, and BCC: fields in the dialog, there are Internal and External fields to which the user can place contacts. When the user clicks OK, the value of the two fields are stored in two arrays:

arrayInternalContacts()
arrayExternalContacts()

with the appropiate contact information.

So the last part of this procedure needs to place this information in the appropriate (Internal or External) table. These values should be entered into the table rows sequentially as found in the array. The user's contact information should remain the the 1st row in its table.

The user may add contacts later, so the procedure should allow for that action. Though I don't yet see a reason, I suppose there may be a situation where the user might want to delete a contact, so this feature would be a "nice to have".

6. Again, the user may save the document at this point or continue working by adding Call History.

7. Call History is simply a method to track/save the details of a call to the contacts and keep track of action items. During a single call, I would like a control to display a Call History UserForm. The reason for the UserForm is to have a larger display area for the user to work with than the table provides.

The UserForm will contain controls to capture the call details and copy them to the Word document when the call is complete. After taking another look, the Call History table needs to split into two table. Up to now, users enter multiple action items per single call, which is OK for a manual system, but unworkable with automation. I attached a new copy of the doc to this post with the separate tables.

So basically I need to copy the data from the UserForm to right places in the two tables. The most recent call gets placed at the top of the Call History table, so that the table stays sorted last call first.

The Action Items are simple copied sequentially to the Action Items table.

8. When the user subsequently opens the document, I want to examine the Action Item due date and make the highlights as indicated in the doc's comments.

There wasn't a place in the Projects forum, where this really belongs. I appreciate help with any of the above features, and all suggestions are welcome!!!

Cheers,
James

[Tommy]

Bear with me a while, I like to bounce things around for a while

On 2 I think a sub should be written for getting the information 2 ways. 1) For New Docs:

user's Active Directory logon name

I don't know what you are saying, but ok, the first time, then use it to check and make sure the correct user opened the doc after the first save maybe? 2) get the information from the doc itself

The only reason is time. I don't know how to get the ADLogon if it is simple I don't think it would be a problem each time, but if it runs an ODBC SQL, a lag will occur which could slow doc the doc opening, maybe, just a thought.
ToolBar is a good idea, something like an Add-in I would think. That way they can open the doc and pick a button and start entering the call hisory. On the Company Name etc, maybe show the listboxes on the new doc and delete them on the first save, this shouldn't change, no need to set us up for problems .

Outlook are you talking a userform that is actually linked to Outlook Contacts? or the table?

The rest is doable.

"Projects Forum" :rofl

[Tommy]

This is a partial.
InternalContacts fills the first line of the contacts table with information passed to it.
PlcCOCntrct places the company information and contract number in formfields
CheckDueDate checks the due date against Now and turns it Yellow if it is due today or green if it is past due

[VBA]
Sub InternalContacts(strADLogon As String, strADEmail As String, strADOPhone As String, strADMPhone As String, strADTitle As String)
Dim TblNum As Long
Dim RowNum As Long
Dim ColNum As Long
TblNum = 2
RowNum = 2
ColNum = 1
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = strADLogon
ColNum = ColNum + 1
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = strADEmail
ColNum = ColNum + 1
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = strADOPhone
ColNum = ColNum + 1
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = trADMPhone
ColNum = ColNum + 1
ActiveDocument.Tables(TblNum).Cell(RowNum, ColNum).Range.Text = strADTitle
End Sub

Sub PlcCOCntrct(SQLCompanyName As String, SQLCompanyContractNum As String)
ActiveDocument.FormFields("CompanyName").Result = SQLCompanyName
ActiveDocument.FormFields("Company").Result = SQLCompanyName
ActiveDocument.FormFields("ContractNumber").Result = SQLCompanyContractNum
End Sub

Sub CheckDueDate()
Dim ERow As Long
Dim DueDate As Date
Dim RightNow As Date
Dim I As Long
ERow = ActiveDocument.Tables(4).Rows.Count
RightNow = Format(Now, "dd/mm/yyyy")
For I = 2 To ERow
DueDate = Format(Left$(ActiveDocument.Tables(4).Cell(I, 3).Range.Text, Len(ActiveDocument.Tables(4).Cell(I, 3).Range.Text) - 1), "dd/mm/yyyy")
'below is not required - it should be marked green when the date is inserted
'If ActiveDocument.Tables(4).Cell(I, 4).Range.Text <> Chr(13) & Chr(7) Then
' ActiveDocument.Tables(4).Cell(I, 1).Shading.BackgroundPatternColor = wdColorGreen
' ActiveDocument.Tables(4).Cell(I, 2).Shading.BackgroundPatternColor = wdColorGreen
' ActiveDocument.Tables(4).Cell(I, 3).Shading.BackgroundPatternColor = wdColorGreen
' ActiveDocument.Tables(4).Cell(I, 4).Shading.BackgroundPatternColor = wdColorGreen
'ElseIf DueDate = RightNow Then
If DueDate = RightNow Then
If ActiveDocument.Tables(4).Cell(I, 4).Range.Text = Chr(13) & Chr(7) Then
ActiveDocument.Tables(4).Cell(I, 1).Shading.BackgroundPatternColor = wdColorYellow
ActiveDocument.Tables(4).Cell(I, 2).Shading.BackgroundPatternColor = wdColorYellow
ActiveDocument.Tables(4).Cell(I, 3).Shading.BackgroundPatternColor = wdColorYellow
ActiveDocument.Tables(4).Cell(I, 4).Shading.BackgroundPatternColor = wdColorYellow
End If
ElseIf DueDate < RightNow Then
If ActiveDocument.Tables(4).Cell(I, 4).Range.Text = Chr(13) & Chr(7) Then
ActiveDocument.Tables(4).Cell(I, 1).Shading.BackgroundPatternColor = wdColorRed
ActiveDocument.Tables(4).Cell(I, 2).Shading.BackgroundPatternColor = wdColorRed
ActiveDocument.Tables(4).Cell(I, 3).Shading.BackgroundPatternColor = wdColorRed
ActiveDocument.Tables(4).Cell(I, 4).Shading.BackgroundPatternColor = wdColorRed
End If
End If
Next
End Sub

[/VBA]

[jamescol]

On 2 I think a sub should be written for getting the information 2 ways. 1) For New Docs: I don't know what you are saying, but ok, the first time, then use it to check and make sure the correct user opened the doc after the first save maybe? 2) get the information from the doc itself

The only reason is time. I don't know how to get the ADLogon if it is simple I don't think it would be a problem each time, but if it runs an ODBC SQL, a lag will occur which could slow doc the doc opening, maybe, just a thought.

No problem Tommy - I expect most folks don't deal with Active Directory much using VBA. AD is a special DB a Windows 2000 or 2003 network uses to hold, among other objects, user account information. When the user log's into the network, AD is used to authenticate him. AD also contains the other attributes I want to capture about the user. Getting AD info using VBA involves using the Windows API, and the query is very fast.

Your point about another user opening the document brings up another issue I hadn't thought about. Since this document will be shared with the other contacts, the code to identify the original user who created the document shouln't run again after the doc is saved the first time. Our Office deployment includes Windows Rights Management, so the user already has a way to determine who can view/edit the document.

ToolBar is a good idea, something like an Add-in I would think. That way they can open the doc and pick a button and start entering the call hisory. On the Company Name etc, maybe show the listboxes on the new doc and delete them on the first save, this shouldn't change, no need to set us up for problems .

So instead of displaying a separate UserForm with the list of customers, just display them from a listbox on the form? I like that idea, and can run the procedure for populating the listbox when the document opens!!

Outlook are you talking a userform that is actually linked to Outlook Contacts? or the table?

VBA code will actually display a built-in Outlook dialog box that display the GAL and Contacts folder. It's the same dialog you see when you click the To: button on an Outlook mail form. The VBA code permits me to customize several controls on the dialog box. So instead of the user seeing the To, CC, and BCC fields at the bottom, they will see Internal and External fields. These fields will let the user select Internal contacts from the GAL and External contacts from his Contacts folder.


Thanks again for the help!

[jamescol]

Tommy,
THANK YOU VERY MUCH! The three procedures worked beautifully. I had to change the date format from "dd/mm/yyy" to "mm/dd/yyyy" to work with my US date, but that was it!!

What a timesaver

James

[Tommy]

I reworked one of the subs. dd/mm :rofl that was debug I wanted to know if it would break it sorry .

The attached file has the toolbar, and command buttons setup and the listbox. I dont think I like the listbox, ok for now I guess. This is a work in progress, that means I am just getting it to work, I have not tweaked or anything.

[jamescol]

Tommy,
Here is an updated document containing the userform. I did not add anything from your previous document yet. Based on the first few procedures you sent, I was able to add some functionality to the document. It's not polished. I also removed the various data calls I am using (except for the OL contacts) and replaced them with hard-coded variables to make it easier to work on different platforms.

In this document:
Should be the Word Doc, Module1, UserForm, ThisDocument module

Completed UserForm Layout
Function InsertTableRow to insert a row in a table
Sub to populate the UserForm Attendees list box and AssignedTo combo boxes
Sub to Save the UserForm data to the Word document
Document_Open procedure for a CommandBar (I like yours better than mine, though)

Let me know what you think. I'll be able to look at your last upload tonight.

I think this is coming along nicely!

Thanks,
James

[Tommy]

I couldn't get the MAPI to work I have no clue why.

Do we want to save the settings of the toolbar from one session to another? That would mean that we would need to write to the registry, or a file. I have code for both, that way the toolbar will be in the last place the user put it from one session to another. User functionality can be a pain but they like it

I am going to look at it some more tonight and see what happens when I merge the 2. If I fry my PC it will just be a good reason to get another one.

I think the below function is backwords, I changed something and want to bounce it off of you. The lrows should always be a 1 (for call history). It should be last one first right? Or did I miss something?

[VBA]
Function InsertTableRow(lRows As Long, IntTableNum As Integer) As Boolean
'lrows should always be 1 this insures that the last call is placed first
On Error GoTo errhandler
InsertTableRows = False
ActiveDocument.Tables(IntTableNum).Rows(lRows).Select
Selection.InsertRowsBelow
InsertTableRow = True
Exit Function

errhandler:
MsgBox Err.Number & ": " & Err.Description

End Function
[/VBA]

[jamescol]

Tommy,
Actually I haven't gotten around to working on the reverse-sort order for the call history table. I just wrote this generic function to insert a row at the bottom of a table. I figured we could add a third arg to tell it whether to add the row at the top or bottom. Something like:

[vba]
Function InsertTableRow(IntTableNum As Integer, Optional lRows As Long, Optional intPos as Integer) As Boolean

InsertTableRows = False

If IsMissing(lRows) and IsMissing(intPos) Then
msgbox "Missing parameter in InsertTableRow. Must use either lRows or
intPos."
Exit Function

Else If !IsMissing(lRows) and !IsMissing(inPos) Then
msgbox "Too many parameters in InsertTableRow. You can only use lRow or intPost, but not both."
Exit Function


End If


On Error Goto errhandler


If intPos = TOP 'TOP defined as CONST TOP = 1 Default is Const BOTTOM = 0
ActiveDocument.Tables(IntTableNum).Rows(2).Select 'not really using lRows in this case
Selection.InsertRowsAbove
Else
ActiveDocument.Tables(IntTableNum).Rows(lRows).Select
Selection.InsertRowsBelow
End if

InsertTableRow = True
Exit Function

errhandler:
MsgBox Err.Number & ": " & Err.Description

End Function
[/vba]

So we send the tablenum and then either the lRow or IntPos. Not sure about how to best handle any errors.

Cheers,
James

[Tommy]

I love kicking things around makes me think. Actually I thought we could pass a 1 for lrows, since we have header rows we will not have to worry, rows.count to add at the end. Or if we need to search pass the row number above where we want to add it.

I am not trying to hammer you or anything, I am just curious and I ask why. SO here I go again. I like the icons, I had thought to do that but where did you get them and how do you know they will be there?

OK I'll get busy
Later

[jamescol]

I haven't thoroughly considered the function yet, really just brainstorming. My initial thought was to design a multi-purpose function that would work for all combinations of row insertions. In my mind, I only had two possible insertions: after the last row or above the second row. That's how I came up with the last interation of the function.

Now I am wondering if there is a simpler way. Perhaps the function could always insert the row at the bottom. If the table happens to be #3- Call History - maybe there is an IF statement to just sort the table in descending order using the Date column as the key. That would at least simplify the arg list. Not so sure which is better on performance.

Of course, that may be short-sighted if there is ever a need to insert a row in the middle of a table. But for now, I only see the two scenarios.

As far as the icons, I like them, but only in combination with the Caption. As you probably see, using only the icons, the toolbar is very tiny. There is a bug in the CommandBar class where the Width property doesn't work on floating toolbars. In my tests, it didn't work docked, either. So adding the captions should get it just right.

It took me a while to figure out how to get the FaceIDs. Finally found a MSFT article about how to get a list (text, not graphical).

[vba]
Dim c As CommandBar, ctl As CommandBarControl

Application.Documents.Add

For Each c In Application.CommandBars
For Each ctl In c.Controls
If ctl.Type = msoControlButton Then
Selection.TypeText "CommandBar Name: " & c.Name & _
" | Caption: " & ctl.Caption & " | Face Id : " & ctl.FaceId
Selection.TypeParagraph
End If
Next
Next

End Sub
[/vba]

Thanks,
James

[Tommy]

No I didn't forget

Just as an update I have managed to grab the contact's name and e-mail. I can display the dialog box but that is as far as I got so far.

[Tommy]

I have the Contacts. I think it needs a form to select the ones you want and to add/delete contacts. I will work on this later. The contacts are coming from the outlook folder contacts.

I also set up the toolbar to remember where it was from one time to the next.

There is also a way to finalize the Action Items. I use the msflexgrid control, is that ok?

Let me know what you think.

Later

[Tommy]

Here is the project with the contacts from the contacts folder. The form is setup to add to either Internal or external contacts. I have an add option but no code for it yet.

[Tommy]

Once Agin

This version has the ability to add or delete contacts from the Outlook Contacts folder. I did not provide a check or workaround for not having a contacts folder. It will work with Outlook but not Outlook Express. The toolbar is now fixed. Some of the forms have been reworked to be more "user friendly". I have not cleaned it up yet, nor have I commented the source. I see one more thing to do and that is to provide a way to "remove" or "add" contacte to/from the tables. After that it should be ready I think. Let me know where I went on a tangent, off base, what was I thinking? :rofl

Oh (No it didn't hurt but I ducked) I forgot I need to fixe the text size and set it to normal text instead of bold.

Later

[jamescol]

Hey Tommy,
I've been swamped at work and just too tired to look at more code at night. I was able to quickly run through your next-to-last version and it looks cool. I like the FlexGrid for archiving the action items!

The only change I see at this point is the procedure for displaying/selecting contacts. The code I developed for that works OK in our environment. Using the built-in Outlook dialog is easier, and, while populating a custom list with contact folder entries is OK, our GAL contains 200,00+ entries and doesn't lend itself to such a function. You don't need to worry about it, since what I have is working OK.

Thanks,
James