Solved: Guidance on direction for building and filling a table

[Waubain]

Sorry for the ramble. I am an end user and typically work in Access. A group of my pharmacist peers have asked if I can automate a form letter sent out to patients who are on a blood thinner, are having surgery soon and must be transitioned to a different medicaton. I made a template which creates a new document and opens a userform with information need to fill in the document.

The tables are giving me the biggest challenge. I attached a jpg of the two possible tables, based on if they need medication once or twice a day (There is more in the template I did not include for brevity). The MOST rows the table can have is 14 including the header. I included my very crude code I have put together. I also recorded a macro in Word just to see what is involved in formatting a table in VBA. Keep in mind that it took about 2 days to read and have a basic understanding what I have already. I left off the initialize sub.

I am looking for recommendations on how to proceed next:

1) Build the entire table, formatting and enter cell contents with vba. This option gives me the most flexibility on which table is inserted, but has the steepest learning curve since I cannot find a good example/tutorial on table building and formatting.

2) Prebuild a formatted table in the template with 14 rows and bookmark each cell and then delete the null rows at the end. This seems the easiest at this point, although I know I would still need to do some formatting of the cells. There is also the problem of two different forms. I would either need two different templates or have two tables one after the other and then delete the table not used

Any thoughts on how to proceed would be greatly appreciated.

Here is the code for the OK button on he userform


Private Sub cmdOK_Click() ' Makes all calculations fills in Document_1 when clicked

Dim strProDate As String
Dim strINRDate As String
Dim dtpWarfDate As Date
Dim strWarfDate As String
Dim intWarfDays As Integer
Dim strEnoxText As String
Dim iRows As Integer
Dim iCols As Integer
Dim tblSchedule As Table
Dim rRange As Range
Dim DosingTable As String
Dim intPreDays As Integer
Dim intPostDays As Integer


'calculates last date to take warfarin
intWarfDays = cboWarfDays * -1
dtpWarfDate = DateAdd("d", intWarfDays, DTPickerProcedure.Value)
strWarfDate = Format(dtpWarfDate, "dddd, mmmm d, yyyy")

'Formats Procedure and INR dates
strProDate = Format(DTPickerProcedure.Value, "dddd, mmmm d, yyyy")
strINRDate = Format(DTPickerINR.Value, "dddd, mmmm d, yyyy")

'creates enoxaparin string
If cboEnoxSig = "BID" Then
strEnoxText = "every morning before 11am and every evening after 5pm"
Else: strEnoxText = "every morning before 11am"
End If

Application.ScreenUpdating = False 'turns off bookmark updating'

'Calculates number of columns and rows for schedule table
intPreDays = cboPreDays.Value
intPostDays = cboPostDays.Value

iRows = intPreDays + intPostDays + 2

If cboEnoxSig = "BID" Then
iCols = 4
Else: iCols = 3
End If

' get bookmark range
Set rRange = ActiveDocument.Bookmarks("DosingTable").Range

'Inserts a table with iCols columns and iRows rows at DosingTable bookmark

Set tblSchedule = ActiveDocument.Tables.Add(Range:=rRange, NumRows:=iRows, NumColumns:=iCols, _
DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)

'Enters data into bookmarks
With ActiveDocument
.Bookmarks("DateofProcedure").Range.Text = strProDate
.Bookmarks("INRDate").Range.Text = strINRDate
.Bookmarks("LastWarfarinDoseDate").Range.Text = strWarfDate
.Bookmarks("EnoxaparinDose").Range.Text = cboEnoxDose.Value & " " & strEnoxText
.Bookmarks("PatientName").Range.Text = "for " & txtPatientName.Value
End With

Application.ScreenUpdating = True 'turns on bookmark updating
Unload Me 'unloads userform

End Sub

[Waubain]

I decided to try Solution #2 and although maybe not the best solution, it is working the way I want.

[Frosty]

I see in this post what you were talking about in the other post.

Another approach to this is to save your two tables as BuildingBlock/Autotext entries (the name depends on what version of Word you are using, 2003 and earlier they were called Autotext entries)... and then insert the one according to what is chosen.

However, that requires an additional level of abstraction (since any modifications to the building block would require a reinsert and a redefinition).

You could accomplish the building of the table on the fly, but it's really not worth the effort (the easiest way to approach that is to simply record the macro as you create the table with the right number of rows and columns, and then format heading rows, etc).

Building formatted tables on the fly isn't generally worth it, IMO. That's what Building Blocks are for-- to give you pre-formatted "chunks" which you can then manipulate. I think the solution you've decided upon is totally fine. It's a pseudo-buildingblock which you simply leave in your template.

The only limitation is that you are only able to access it from the macro... whereas it can some times be desirable to use a building block elsewhere, if you weren't using the macro at all (since you can then access the building block additional times).

The only addition I would make to your code (in this thread and the other), is to make sure you aren't relying too much on automatic conversions of data types. Your routines will fail when the .Value of a user form control hasn't been filled in (so it may be Null, or it may be an empty string, depending on the type of control)... and if your routines are expecting an integer or a date, that will cause you errors.

[Frosty]

And as a post-script... this is a very good first effort in Word VBA :)

[Waubain]

Thanks.

All entries into the userform are comboboxes and the dates are chosen with the MS DatePicker calender and all entries are validated with routines to minimize these kind of errors. I ususally work with Access and the little I have learned is that it takes 5 times more code to prevent user errors than code for processing what you want to accomplish. The girls in the office made this request so I thought I would give it a try. Was harder than I thought since the controls/objects are different than Access

I will look further into the BuildingBlock (using 2007/2010) which may solve a very small annoyance. The two tables are not quite like the picture. They are separate by one space. If the first table is chosen, then there is an extra space after the table before the following text, and if the second table is chosen then their is an extra space before the table and the proceeding text.

Thanks again.

[fumei]

"The two tables are not quite like the picture. They are separate by one space."

Tables must be separated by at least one space, otherwise they join. As a hint to make this space easier to locate (and thus identifying the individual tables) it is a good idea to have a dedicated style. I use a dedicated style named TblSpacer to separate tables. This can be used as both a before and after style. In addition because it is a style you can define its quality such a size. Making it 1 pt makes tables seem to really butt up against each other, but of course they do not.

[Waubain]

Fumei,

Thanks for tips. I found out about the "at least one space" the hard way. Your way of a dedicated style sounds a lot easier than recording a macro to see how many key strokes down and delete to try to get the extra spaces removed, which is what I was going to try today.