none
Connect to SharePoint in Excel

    Question

  • Is it possible to create an OLEDb connection to a sharepoint list from Excel using VBA.

    I know I can export a list to excel which creates a connection for me however this connection is unreliable at best and is frequently deleted by excel.  I need to program the connection where it will be reliable.  The user will be opening the excel workbook which needs to connect to sharepoint to retrieve data.

    Tuesday, June 26, 2012 11:57 AM

Answers

  • Connecting directly to SharePoint databases is not supported by Microsoft. Only using the SharePoint API or Web Services is supported.

    If OLEDB isn't a requirement, you can give the Web Services route a try: http://msdn.microsoft.com/en-us/magazine/cc163837.aspx

    As well, what I have done in the past, is use a tool like SharePoint List Association Manager (SLAM: http://slam.codeplex.com/) to auto sync a SharePoint list with a SQL Server table, and import that table into my Excel file, or you can then use OLEDB to connect to the table for real-time SharePoint data as SLAM updates the table for every add/change in the SharePoint list.

    Hope that helps.

    Thursday, June 28, 2012 6:31 PM

All replies

  • Hi jd_hancock,

    Thanks for posting in the MSDN Forum.

    It's possible in theory. You can connect sharepoint via ADODB object, however you must aware of the Sharepoint's SQLServer database's structs.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, June 27, 2012 2:45 AM
    Moderator
  • So creating a connection object to SharePoint programmatically is not possible? 

    If I am in a sharepoint list/doc lib, i can open it in Excel which creates a connection (Again, this is unreliable at best).  This type of connection can only be created by SharePoint and not programmatically?  There are examples of using mapped drives however we are not permitted to map network drives to SharePoint.  Apparently a huge security risk and not a good practice. 

    Thanks for the info.

    Wednesday, June 27, 2012 11:07 AM
  • Hi jd_hancock,

    It's based on my experience that is hard to do and great security risks are behind of such action.

    Have a good day,

    Tom 


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, June 28, 2012 5:49 AM
    Moderator
  • Connecting directly to SharePoint databases is not supported by Microsoft. Only using the SharePoint API or Web Services is supported.

    If OLEDB isn't a requirement, you can give the Web Services route a try: http://msdn.microsoft.com/en-us/magazine/cc163837.aspx

    As well, what I have done in the past, is use a tool like SharePoint List Association Manager (SLAM: http://slam.codeplex.com/) to auto sync a SharePoint list with a SQL Server table, and import that table into my Excel file, or you can then use OLEDB to connect to the table for real-time SharePoint data as SLAM updates the table for every add/change in the SharePoint list.

    Hope that helps.

    Thursday, June 28, 2012 6:31 PM
  • When connecting to a SharePoint list from Excel, you can initiate the connection from SharePoint using the "Export to Excel" option.

    This creates a connection object (in Excel) that can be refreshed each time you open the Excel workbook.

    All I'm trying to do is create this connection using VBA from Excel.  When I create a connection and use the same connection string SharePoint populates in teh connection, it throws multiple errors.  I just haven't been able to provide the right properties to the connection to create this.  I'm sure it can be done.  Just looking for an example.

    Thanks all for your help.

    Monday, July 02, 2012 11:51 AM