Using Excel tables and formula in a word document

nbooth1983 · #1 04-13-2018, 08:29 AM

I work in a vets and have a client handout with information about pet food which is a word document. We use these handouts to inform clients of how much different sizes bags of dog food will cost them.We have been calculating this by hand and writing the information on a printed sheet previously, but I now want to do this digitally.

I have created a "cost calculator" in excel which will calculate the cost per day of dog food based on the daily feeding allowance. The idea I have is to input the daily feeding amount (which will be different for each dog) and the formula will do the rest. This works fine in the excel sheet I have created.

What I wanted to know is if there is a way to input this formula into word, or to input the text info into excel but format it like a word document as I want to add logos and other images.

I have attached sample documents which will hopefully make it a bit clearer as to what I want than my garbled post! Thanks in advance for any help.

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