Using Excel tables and formula in a word document

Guessed · #2 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Revise tables in a word document based on a reference table in excel	modiria50989	Word VBA	1	08-29-2017 03:07 PM
Adding tables to Created word document whilst other word document open Help	rpb925	Word VBA	18	03-30-2016 04:45 PM
Include Excel Tables in Book Document	SQLUSA	Word	20	07-16-2012 03:59 AM
Copying Multiple tables from excel into a single word document	dineshtgs	Word Tables	1	04-07-2011 01:27 AM
Linking Excel Pivot Tables in a Word Document	wmarsh3561	Word Tables	0	11-17-2009 06:29 AM