locked
Linking 1 excel sheet with 3 sharepoint lists RRS feed

  • Question

  • Hello, first of all i want to say that i work with sharepoint 2007.

    The topic says it all, is it possible to link, syncronize 1 excel sheet with more than 1 sharepointlists.

    Cuz what i have is a parent and then 3 sub websites, and each website needs to have a list that is synchronized with the same excel sheet and have the same data.

    What ive now done is just made 3 spreadsheets with the same date and link each sheet with different subwebsite list, what makes some problems arise, like synchronizing all 3 lists differently, or even if you can make it work with vba, from sharepoint it will be more difficult.

    I thank you in advance for the help.

    • Moved by Wayne Fan Tuesday, October 25, 2011 7:09 AM It's SharePoint 2007 issue, thanks. (From:SharePoint 2010 - General Questions and Answers)
    Monday, October 24, 2011 6:37 AM

All replies

  • Hi Alnedru,

     

    The answer to your question is Yes. And it doesn’t need to write VBA code.

     

    Theory:

    When you click the “Export to Excel” button in the sharePoint list, a .iqy file will be downloaded to your local client. You client the .iqy file, you can get the List information.

    The .iqy is a “data connection” file. When you edit it using VS 2010, you can get the code like this:

    WEB

    1

    http://servername/_vti_bin/owssvr.dll?XMLDATA=1&List={ae2a8401-4104-4840-a016-96521863a2f6}&View={26477CB8-A6C3-43FD-8796-DC1F2A67F9BE}&RowLimit=0&RootFolder=%2fLists%2fTasks

     

    Selection={ae2a8401-4104-4840-a016-96521863a2f6}-{26477CB8-A6C3-43FD-8796-DC1F2A67F9BE}

    EditWebPage=

    Formatting=None

    PreFormattedTextToColumns=True

    ConsecutiveDelimitersAsOne=True

    SingleBlockTextImport=False

    DisableDateRecognition=False

    DisableRedirections=False

    SharePointApplication=http://servername/_vti_bin

    SharePointListView={26477CB8-A6C3-43FD-8796-DC1F2A67F9BE}

    SharePointListName={ae2a8401-4104-4840-a016-96521863a2f6}

    RootFolder=/Lists/Tasks

     

    So you can create a custom connection file, and then add the connection file to Excel, show the data in list item

     

    Steps:

    1.       Create two lists in SharePoint 2010 for test, we name them “ListA” and “ListB”;

    2.       Click the “Export to Excel” in “ListA”, you will download the “owssvr.iqy” from the server;

    3.       Rename it to “listA.iqy”;

    4.       Repeated the steps 2 and 3 on listB;

    5.       Create a new Excel, click the “connections” ribbon on the “Data” ribbon tab;

    6.       Find the “listA.iqy” and “listb.iqy” in your local server, add them to the Excel Conenctions;

    7.       Click the “Existing Connections” and double click the “ListA” connection, the data table will be shown in the current work sheet;

    8.       Repeat step 7 for ListB.

    Now, you have added two connections to the Excel. And the data can be updated if you click the “Refresh All” button.

    Tuesday, October 25, 2011 7:50 AM
  • Hello Wayne Fan and ty for the reply.

    Altho it isnt what i was searching for. You describe how i can link sharepoint lists FROM sharepoint TO excel, altho what i have is Excel file and i need to publish it in 3 different lists and let say after it if ill add a row in an excel or change some value and click on synchronize it will be synchronized with 3 different lists in sharepoint, that are allocated in 3 different sub sites.

    Cuz what i noticed is that when i make a list in excel and i say publish then i have a chebox saying link that excel with a new list, when i click it then it will be linked with it and after adjusting somthing and synchronizing the changes will be made also in sharepoint list cuz it is binded. But let say i wanna publish that list again, in other sub site i also gonna link it with new list in sharepoint so other connection with previous link will be broken, same goes if i dont check the checkbox it will not be linked with the new sharepoint list but only with he previous. So i always have only one connection/link to a sharepoint list.

    What you said i when i have a sharepoint list and then im gonna export it to excel, i think it is a bit different.

    So im just wondering is there a more easier sollution to my problem, or i really need to download some document from sharepoint iqy and adjust them? Some more detailled information would be welcome.

    Thanks in advance 

    Friday, October 28, 2011 5:58 AM