No announcement yet.

Script to grab specific Sharepoint Excel data and create word doc from template?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Script to grab specific Sharepoint Excel data and create word doc from template?

    Latest project:

    Sharepoint contains a pretty big (and frequently updated) Excel (2007 I believe) spreadsheet with various data points. There is a column with a unique fieldID as well as dates (but the date isn't a unique field).

    Currently each day we're filtering the spreadsheet by date and grabbing the necessary data from all of the rows with the current date in it. We're not grabbing ALL of the data in the row, just from certain columns, but the process is the same regardless of how many rows contain today's date.

    Example: Think of a spreadsheet that contains X # of specials of the day for each day for the rest of the year. It contains the date, the menu item, it's price, the ingredients, how to make it, etc. All we need from each row that has today's date is the unique ID for each row, the name of the menu item, and the price.

    Once we have obtained the data from each row that we need, we manually copy a Word 2007 document and save it as <FIELD 8>_Template1.docx. This is done for each row for that day. From there we copy the data into specific fields in that docx file.

    Example: Think of the template as a default layout for a note for each special to be placed in each menu. Multiple specials of the day = multiple notes using the same original template.

    There's only a couple of rows for each day, and it's only a couple of fields per row, but we know we're going to have to do this more and more each day. As you can tell, this screams AUTOMATION! Unfortunately, I've never done any scripting like this, let alone with the data source being a live Excel spreadsheet in Sharepoint.

    Anyone have any suggestions as to how we can accomplish this, or where to start, or if it's even possible? I'd LOVE to slap all of the data in a SQL DB and just create a web interface to it for the updating of the data and extraction of data and whatnot, but that's a no go.
    ** Remember to give credit where credit is due and leave reputation points where appropriate **

  • #2
    Re: Script to grab specific Sharepoint Excel data and create word doc from template?

    This is probably out of my depth... but oh well.

    Off the top of my head, I see a fewpossibilities. First, you could use a scheduled ADO script to connect to the Excel file via OLE DB. From there it's just a matter of a simple SQL statement to get the data. Of course, you'd then have to figure out how to insert the data into the docx file.

    Other options include creating a word document that has an excel spreadsheet embedded and then from that embedded spreadsheet open a datasource connection to the master spreadsheet and do some querying to bring over the data you want. There would have to be some scheduled VBA code. you might even be able to hack some kind of demented mail merge using the master excel spreadsheet as a data source and only selecting the data you want.

    However, any way I look at it, it seems that your excel spreadsheet might need to be changed to an Access database. I've had a hard time connecting to an Excel spreadsheet as a datasource. An Access backend makes it a little easier.

    You could also go hardcore and fire up Visual Studio to do something like this. Eeew.
    Wesley David
    LinkedIn | Careers 2.0
    Microsoft Certifications: MCSE 2003 | MCSA:Messaging 2003 | MCITP:EA, SA, EST | MCTS: a'plenty | MCDST
    Vendor Neutral Certifications: CWNA
    Blog: || Twitter: @Nonapeptide || GTalk, Reader and Google+: [email protected] || Skype: Wesley.Nonapeptide
    Goofy kitten avatar photo from Troy Snow:


    • #3
      Re: Script to grab specific Sharepoint Excel data and create word doc from template?

      I'd go with the OleDB way for data extraction. Save the data as CSV so you can easily enough manipulate it later on.

      The Word part is tricky, but how about some creative macros? You could create a macro that reads the CSV, inserts data into bookmarked locations and saves & closes the whole thing.