PDA

View Full Version : Solved: Fill in table column with loop and date calculation



Waubain
02-26-2012, 09:00 PM
This is my first attempt with Word VBA. I am trying to automate a form letter that contains a table. I want to fill in the first column of the table with dates starting at Row(3). DTPickerProcedure, cboPreDays, and cboPostDays all come from a user form. This routine is a part of a larger routine, and variables are declared earlier.

I am receiving an "Invalid use of the Property" at .Cells in the following line of code

ActiveDocument.Tables(1).Rows(3).Cells
I know the routine has more problems because it is not going to count properly the way it is written but I cannot get pass the error.

The way is should work is if
DTPickerProcedure.Value = 3/5/2012
cboPredays = 2
cboPostday = 2

The dates starting at row(3) should be
cell(3,1) 3/3/2012
cell(4,1) 3/4/2012
cell(5,1) 3/5/2012
cell(6,1) 3/6/2012
cell(7,1) 3/7/2012

Here is what I have so far:

Private Sub FillDateColumn()

Dim intPreDays As Integer
Dim intPostDays As Integer
Dim intCounter As Integer
Dim irows As Integer

intPreDays = cboPreDays.Value
intPostDays = cboPostDays.Value
irows = (intPreDays + intPostDays + 1)

For intCounter = 1 To irows
ActiveDocument.Tables(1).Rows(3).Cells
Cell(1).Range.Text = DateAdd("d", -(intPreDays), DTPickerProcedure.Value)
Next

End Sub

I need the date output to be in this format, but that is not the primary problem.

Format(DTPickerProcedure.Value, "dddd, mmmm d, yyyy")
Thanks for any suggestions.

Frosty
02-27-2012, 02:39 PM
Does this help? There are many ways to tackle this, so this is just a proof of concept. I think the main take-away is to know that actual dates are formatted as the number of days from the start of the Common Era. So you can easily add/subtract days from dates without too much wiggling.

So if you get your "real" start date (by converting your DTPickerProcedure.Value to a date variable, and then subtracting your PreDays, this becomes pretty easy).

Sub ProofOfConcept()
Dim dtDatePicker As Date
Dim dtStart As Date
Dim iPreDays As Integer
Dim iPostDays As Integer
Dim oTable As Table
Dim i As Integer
Dim iRowToStartAt As Integer

'these values come from your form
dtDatePicker = #3/5/2012#
iPreDays = 2
iPostDays = 2

'and this is the row to start at (you said 3 in your post)
iRowToStartAt = 3

'get our actual start date
dtStart = dtDatePicker - iPreDays

'this is just the table for a proof of concept -- but the number of rows is key
'since we use that count below
Set oTable = ActiveDocument.Tables.Add(Selection.Range, iPreDays + iPostDays + iRowToStartAt, 2)

'inserting into the document
With oTable
'since we're starting at row 3
For i = iRowToStartAt To oTable.Rows.Count
'insert the date text, minus the start row
.Cell(i, 1).Range.Text = Format(dtStart + (i - iRowToStartAt), "dddd, mmmm d, yyyy")
Next
End With

'just in case you do this on something other than a blank document
oTable.Select
End Sub

Waubain
02-27-2012, 07:40 PM
Frosty,
Thanks for the suggestions. It wasn't clear in my post, but I have a table that is already build in my template. Actually there are two tables and one gets deleted based on a userform entry. The rows are also deleted to match the pre and post days. This maybe wasn't the best but worked for my first time doing something in Word.

Here is the sub for structuring the table

Private Sub FourColumnTable()

'Calculates number of columns and rows for schedule table

Dim intPreDays As Integer
Dim intPostDays As Integer
Dim intCounter As Integer

intPreDays = cboPreDays.Value
intPostDays = cboPostDays.Value

ActiveDocument.Tables(1).Delete

For intCounter = 1 To (7 - intPostDays) ' deletes higher rows
ActiveDocument.Tables(1).Rows(9).Delete
Next
For intCounter = 1 To (5 - intPreDays) ' deletes lower rows
ActiveDocument.Tables(1).Rows(3).Delete
Next

' call TablesDates sub to fill in dates

TableDates

End Sub


And here is your modified code to get it to insert the dates into table.

Private Sub TableDates()

Dim dtDatePicker As Date
Dim dtStart As Date
Dim iPreDays As Integer
Dim oTable As Table
Dim i As Integer
Dim iRowToStartAt As Integer

'these values come from your form
dtDatePicker = DTPickerProcedure.Value
iPreDays = cboPreDays.Value

'and this is the row to start at (you said 3 in your post)
iRowToStartAt = 3

'get our actual start date
dtStart = dtDatePicker - iPreDays

Set oTable = ActiveDocument.Tables(1)
'inserting into the document
With oTable
'since we're starting at row 3
For i = iRowToStartAt To oTable.Rows.count
'insert the date text, minus the start row
.Cell(i, 1).Range.Text = Format(dtStart + (i - iRowToStartAt), "dddd, mmmm d, yyyy")
Next
End With

End Sub


Thanks again...Waubain