none
Open Excel file using Jet code

    Question

  • Hello Expert,

    I am trying to access an Excel file hosted in SharePoint Document Library using Jet code.

    my problem is that, in the Oledb connection string, how can I let the datasource property of the connction string to be http url (Document Library URL) instead of physical path ?

    I do not want to use Excel services, I want to use Jet code.

    Thanks

    Wednesday, July 07, 2010 3:32 PM

Answers

All replies

  • Hello,

    Jet OLE connection can only handles the local Excel files. It cannot directly connect to an Excel workbook stored in web server like Sharepoint.

    The workaround would be download the document from Sharepoint site to your local and then connect to the local version.

     

    Best regards,
    Ji Zhou - MSFT
    Microsoft Online Community Support


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, July 08, 2010 12:24 PM
    Moderator
  • Thanks for resonse, I tried to access the list using the UNC path as  you can see in my sample code below, and it works  only on my local test project (i.e only in the asp.net webapplication project). But when I deployed it into Sharepoint, it did not work! It gives me error “The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.”

      

     

          Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\myShrSite\TestDocList\tstExlJet.xlsx;Extended Properties=Excel 12.0"

           

     

            Dim oledbConn As OleDbConnection = New OleDbConnection(connString)

     

            oledbConn.Open()

     

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Student Info$] Where [Grade] > 60", oledbConn)

     

            Dim oleda As New OleDbDataAdapter()

     

            oleda.SelectCommand = cmd

     

            Dim dt As New DataTable

     

            oleda.Fill(dt)

           

            grid1.DataSource = dt

            grid1.DataBind()

    Thursday, July 08, 2010 8:42 PM
  • Hello,

    My original suggestion is to download the file firstly into the local temprorary folder. We can use WebClient class to do that.

    http://www.thereforesystems.com/code-sample-programmatically-download-file-using-c/

    After that, we can use OleDbConnection to connect to a local file directly. This should be a quick and easy solution to the problem.

     

    And as to the exception you mention in your post, I see some discussion in the web and it looks like a permission issue, see if this helps,

    http://www.devnewsgroups.net/adonet/t13135-cant-open-oledb-connection-access-database.aspx

    http://support.microsoft.com/default.aspx?scid=kb;en-us;316675

    The account runs the codes may not have the permission to access that sharepoint.

     


    Best regards,
    Ji Zhou - MSFT
    Microsoft Online Community Support


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, July 15, 2010 10:03 AM
    Moderator