Inserting Excel Tables into Data Driven Pages

[KarynMcDonald]

Hi,

I am creating a map series and would like to insert an excel table for each page. (in ArcMap10) It would be great to have the excel spreadsheet to dynamically update for each map page but I haven't been able to work out how to do this.   The way I am going about it (and probably not the most efficient) is by creating a table in excel for each map page.  I then copy and paste the excel table into word and save.  In ArcMap, I am then going Insert>Object and inserting the word document into the map (as a link). The table comes in correctly.  (I have tried to link directly to excel but it never works properly as it won't recognise the data range I am interested in).

When I insert the tables I insert them into data View rather than layout view in a logical location for each page.  This means that when I print the series, the tables will be different for each page.  I am however now having issues with my workspace and I am guessing that having lots of linked excel tables in an MXD is causing problems.  I could go back and copy and paste the tables as an image rather than a link but perhaps that will cause the similar problems. There are approximately 150 maps in each series (and we are doing about 17 series).  Not every map page has an attribute table, only about half.   Has anyone done something similar successfully or have a better solution?

Thanks very much for your help!

[DavidWatkins]

Hi Karyn,

Do you have access to the Production Mapping extension? There is a dynamic table called the Graphic Table Element that is part of this extension that works well in conjunction with data driven pages.  It makes it possible to include a table on your map layout that updates based on the content of the current page extent.

David

[WilliamContreras]

Please refer to the following links for more details regarding GTE:

What is a graphic table element?
http://help.arcgis.com/en/arcgisdesk...009n000000.htm

Creating an interactive table
http://help.arcgis.com/en/arcgisdesk...000006v000000/

[KarynMcDonald]

Hi,  Sorry for the delayed response.  Thanks very much for your replies.

We don't have the Production Mapping extension but I am downloading the free trial and will have a play with it. 

Is there any way of doing this without the production mapping extension?
Thanks again,
Karyn

[JeffBarrette]

Not easily.  It can be done with arcpy.mapping but it would require that you author a table outline in ArcMap using line graphics.  Then you would populate dynamic text to fill the columns in the table.  I'm working on getting a sample out on the resource center.  Once I get data permissions, I'll put it up as soon as I can.

Attached is a static table graphic (of grouped line elements).  There are actually 3 tables, each with 3 columns.  I have a total of 3 text elements, one for each column in each table.  I read the rows from a GDB table and dynamically populate the text element with line breaks (to create the rows).  I can fit up to 15 rows of data onto each table.  If there are more than 15 rows, I populate the next table, etc.

Here is the code that populates the table:

for row in allRows:
    if count < 15:
      tab1Col1Txt.text = tab1Col1Txt.text + row.getValue("DATE") +"\n"
      tab1Col2Txt.text = tab1Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab1Col3Txt.text = tab1Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 15 and count < 30:
      tab2Col1Txt.text = tab2Col1Txt.text + row.getValue("DATE") + "\n"
      tab2Col2Txt.text = tab2Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab2Col3Txt.text = tab2Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 30 and count < 45:
      tab3Col1Txt.text = tab3Col1Txt.text + row.getValue("DATE") + "\n"
      tab3Col2Txt.text = tab3Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab3Col3Txt.text = tab3Col3Txt.text + row.getValue("MADE_BY") + "\n"

Jeff

[EricMcPhee]

I would really like to see a sample of this on the Resource Center. I do not have the Production Mapping extension either, but I have a need to update a table in a layout based upon my data driven pages. Can you post your entire python code?

[JeffBarrette]

A sample has been placed onto the Resource Center.

http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=8C8E6EB6-1422-2418-A0B8-6AA61...

Jeff

[KateNewell]

Is there a way to insert a word document based on a specific page in a map document?

Not easily.  It can be done with arcpy.mapping but it would require that you author a table outline in ArcMap using line graphics.  Then you would populate dynamic text to fill the columns in the table.  I'm working on getting a sample out on the resource center.  Once I get data permissions, I'll put it up as soon as I can.

Attached is a static table graphic (of grouped line elements).  There are actually 3 tables, each with 3 columns.  I have a total of 3 text elements, one for each column in each table.  I read the rows from a GDB table and dynamically populate the text element with line breaks (to create the rows).  I can fit up to 15 rows of data onto each table.  If there are more than 15 rows, I populate the next table, etc.

Here is the code that populates the table:

for row in allRows:
    if count < 15:
      tab1Col1Txt.text = tab1Col1Txt.text + row.getValue("DATE") +"\n"
      tab1Col2Txt.text = tab1Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab1Col3Txt.text = tab1Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 15 and count < 30:
      tab2Col1Txt.text = tab2Col1Txt.text + row.getValue("DATE") + "\n"
      tab2Col2Txt.text = tab2Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab2Col3Txt.text = tab2Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 30 and count < 45:
      tab3Col1Txt.text = tab3Col1Txt.text + row.getValue("DATE") + "\n"
      tab3Col2Txt.text = tab3Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab3Col3Txt.text = tab3Col3Txt.text + row.getValue("MADE_BY") + "\n"

Jeff

[JeffBarrette]

I don't know of a way to do this with Python.  OLE objects don't have data source properties.  A possible alternative would be to save the DOC as a high resolution time and insert it as a picture element.  Picture elements have a .sourceImage property that can be changed as you go from page to page.

Jeff

[BillLotz]

Hi Karyn,

Do you have access to the Production Mapping extension? There is a dynamic table called the Graphic Table Element that is part of this extension that works well in conjunction with data driven pages. It makes it possible to include a table on your map layout that updates based on the content of the current page extent.

David

This is ridiculous, the cost of software, both initial and ongoing, and you want us to purchase yet another extension.

Looking for a way out of the ESRI quicksand......