Microsoft Office Forums - View Single Post - Using Excel tables and formula in a word document

Guessed · 04-13-2018, 09:16 PM

The attached example shows how you can do it in Word without Excel. It uses content controls and allows you to change the daily allowance or cost per bag and the other values recalculate with a macro as you leave the content control.
The code included in the file looks like this

Code:

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  Dim iAmt As Long, iPrice12 As Long, iPrice4 As Long, iDays12 As Long, iDays4 As Long
  Dim iDailyCost12 As Long, iDailyCost4 As Long
  Select Case aCC.Title
    Case "Daily Amount", "BagCost12", "BagCost4"
      iAmt = ActiveDocument.SelectContentControlsByTitle("Daily Amount").Item(1).Range.Text
      iPrice12 = ActiveDocument.SelectContentControlsByTitle("BagCost12").Item(1).Range.Text
      iPrice4 = ActiveDocument.SelectContentControlsByTitle("BagCost4").Item(1).Range.Text
      iDays12 = 12 * 1000 / iAmt
      ActiveDocument.SelectContentControlsByTitle("Days12")(1).Range.Text = Format(iDays12, "#,###")
      iDays4 = 4 * 1000 / iAmt
      ActiveDocument.SelectContentControlsByTitle("Days4")(1).Range.Text = Format(iDays4, "#,###")
      iDailyCost12 = 100 * iPrice12 / iDays12
      iDailyCost4 = 100 * iPrice4 / iDays4
      ActiveDocument.SelectContentControlsByTitle("Daily12")(1).Range.Text = Format(iDailyCost12, "#,###")
      ActiveDocument.SelectContentControlsByTitle("Daily4")(1).Range.Text = Format(iDailyCost4, "#,###")
  End Select
End Sub