locked
Export to Pre-Formatted Excel Spreadsheet RRS feed

  • Question

  • I have a List that I need to create a hardcopy of each week for distribution.  I need to be able to export to a spreadsheet that already has the cell attributes I need e.g. cell width and conditional formatting.  I would like to  do this using native functionality but it is so important that I will do anything necessary.

    Sunday, February 21, 2010 12:43 PM

Answers

  • Try going to the list > Actions > Export to Excel.  This will open up a new Excel 2007 file with a data connection pointing to the list.  You can choose to import the data as a table and then setup the cell widths and conditional formatting.  You can save this spreadsheet on the network or in a doc library.  You cannot however refresh the data in the spreadsheet using Excel services (Excell web access).  When you need to refresh the data you can go Data > Refresh All.  This solution includes manual steps, so it may not suffice for what you need.  It is a start.
    Mike Huguet
    http://www.geekswithblogs.com/mikehuguet
    http://twitter.com/mhuguet
    Sunday, February 21, 2010 7:09 PM

All replies

  • Try going to the list > Actions > Export to Excel.  This will open up a new Excel 2007 file with a data connection pointing to the list.  You can choose to import the data as a table and then setup the cell widths and conditional formatting.  You can save this spreadsheet on the network or in a doc library.  You cannot however refresh the data in the spreadsheet using Excel services (Excell web access).  When you need to refresh the data you can go Data > Refresh All.  This solution includes manual steps, so it may not suffice for what you need.  It is a start.
    Mike Huguet
    http://www.geekswithblogs.com/mikehuguet
    http://twitter.com/mhuguet
    Sunday, February 21, 2010 7:09 PM
  • Hmmm ... well I have definitley learned something useful here, so thanks for that.

    But ... although the conditional formatting seems to "stick", column width does not.  Is there any way to make that happen -- that's a big issue for me.

    Also, the connection file seems to be sitting on my hard drive which means this is local to me, and infact to my PC ... is there someway I can put it on the network for others to use?

    Many thanks! 
    MF
    Tuesday, February 23, 2010 10:56 PM
  • I'm not sure about the column widths.  You can export the connection and save it to a data connection library in SP.  Unfortunately, you cannot use Excel Services because it does not support rendering of a data source pointing to a SP list.
    Mike Huguet
    http://www.geekswithblogs.com/mikehuguet
    http://twitter.com/mhuguet
    • Proposed as answer by Mike Huguet Wednesday, March 3, 2010 5:04 AM
    Wednesday, February 24, 2010 3:42 AM