Basic calculations using numbers within word document

[Phil Smith]

Hi all and a Happy New Year to everyone. ::hi:

Before I leap head first into my problem, I first of all need to know if you can perform basic calculations on numbers found within a Word document.

For example:

Arr Dep
Line 1 10.58 10.59
Line 2 11.25 11.27
Line 3 11.35 11.35
Line 4 12.07 12.19
Line 5 12.30

What I woulod I like to achieve is if the difference between the arrival and departure times is 2 minutes or greater with the difference being x, after the departure time, I would like to insert "x min wait".

So, using the example above...


Arr Dep
Line 1 10.58 10.59
Line 2 11.25 11.27 2 min wait
Line 3 11.35 11.35
Line 4 12.07 12.19 12 min wait
Line 5 12.30

Is this even possible to do in Word VBA or am I looking for something impossible?:dunno

If it is possible, I can go into further detail.

Thank you in advance people of the Forum.

Phil

[gmaxey]

Phil,

Yes, Happy New Year.

It is possible. Assuming your data was arranged in a 4 column table with the Arr. Time is column 2 and Dep. Time in column 3:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 1/2/2019
Dim oTbl As Word.Table
Dim lngIndex As Long
Dim var1, var2, var3

Set oTbl = ActiveDocument.Tables(1)
For lngIndex = 2 To oTbl.Rows.Count
var1 = fcnCellText(oTbl.Rows(lngIndex).Cells(2)): var2 = fcnCellText(oTbl.Rows(lngIndex).Cells(3))
If IsDate(var1) And IsDate(var2) Then
var3 = DateDiff("n", var1, var2)
Select Case var3
Case Is >= 2: oTbl.Rows(lngIndex).Cells(4).Range.Text = var3 & " min. wait"
Case Else
oTbl.Rows(lngIndex).Cells(4).Range.Text = ""
End Select

End If
Next
lbl_Exit:
Exit Sub
End Sub

[macropod]

You can even do it without VBA, though I'm not sure you'd want to in this case. To see how to do this and just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
http://www.msofficeforums.com/word/38719-microsoft-word-date-calculation-tutorial.html
or:
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the item titled Add Or Subtract Two Time Periods. Do read the document's introductory material.

[Phil Smith]

Thanks to both gmaxey and macropod for their replies. I need to get some more info on here as the layout is that each journey is in its own table. How could I get the example document available for you to see exactly what I am working with?

[macropod]

You could click on the Reply button, then attach a sample document via 'Manage Attachments' button located below the posting window.

[gmaxey]

If all of the tables are laid out as per the original definition then it would just be a matter of looping through each table:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 1/2/2019
Dim oTbl As Word.Table
Dim lngIndex As Long, lngTbl as Long
Dim var1, var2, var3
For lngTbl = 1 to ActiveDocument.Tables.Count
Set oTbl = ActiveDocument.Tables(lngTbl)
For lngIndex = 2 To oTbl.Rows.Count
var1 = fcnCellText(oTbl.Rows(lngIndex).Cells(2)): var2 = fcnCellText(oTbl.Rows(lngIndex).Cells(3))
If IsDate(var1) And IsDate(var2) Then
var3 = DateDiff("n", var1, var2)
Select Case var3
Case Is >= 2: oTbl.Rows(lngIndex).Cells(4).Range.Text = var3 & " min. wait"
Case Else
oTbl.Rows(lngIndex).Cells(4).Range.Text = ""
End Select

End If
Next lngIndex
Next lngTbl
lbl_Exit:
Exit Sub
End Sub

[Phil Smith]

Here is a sample document. Not every document has the same number of tables, but each table would have the same layout.

23511

Thanks!

[Phil Smith]

2nd time lucky...

23513