locked
Importing SharePoint data into Excel 2007 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have been searching for a while now on how to import data in a SharePoint list into Excel 2007 that was similar to XML data import in Excel 2003.  I can bring in the data by Exporting to Spreadsheet from SharePoint and writing the VBA to connect to the list but I am finding a major problem that I know there must be a solution for.

    Export to spreadsheet from SharePoint from a public view works like a charm, however I don't want all my users to have views that display all the columns however certain fields are used for Excel reporting.  If I try and export to spreadsheet from a personal view with the columns I need, use the same VBA code then I can bring in the data no problem (which would make sense) but other users cannot bring in the data (which also makes sense).

    Anyone know what the work around for this is?  I can't imagine Microsoft would kill the same function 2003 had with XML --> Data which allowed for any columns to be brought it.

    Keep in mind I am not concerned with two-way synchronization for this.  The article I used to first bring in the data was: http://support.microsoft.com/kb/930006.

    Any help is greatly appreciated.


    • Edited by Nilios Friday, August 17, 2012 2:28 AM
    Friday, August 17, 2012 2:28 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    HI,

    Instead of making this complex I would suggest you to  create a public view with fields you want for your public users.

    I hope this will help you out.

    Thanks, Rahul Rashu

    Friday, August 17, 2012 3:14 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rahul,

    Thank you for the prompt response.  My intent is not to have a public view for a few reasons:

    1. There could be as many as 70 columns that need to be imported into Excel and would prefer not to have a public view that is unnecessary (nobody is going to want to scroll horizontally that much).

    2. There are some columns that I would not want visible to all public users.

    Based on your response, is it safe to safe that Microsoft 2007+ can only import SharePoint data based on a view from a list and not specified columns from a list like in 2003?

    I'm guessing this is why Excel Services was created.

    Friday, August 17, 2012 3:44 AM