0

I am trying to read a file from sharepoint location. The code works absolutely fine when reading from local drive. Please also note that the file type is .xlsx If I convert the file into .xls the code works fine regardless whether the file is on the server or on the local path.

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & dataPath & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"

Error received

External table is not in the expected format.

timiTao
  • 1,417
  • 3
  • 20
  • 34
Junaid Arshad
  • 1
  • 3
  • may be the connection string. check here https://stackoverflow.com/questions/18144838/vba-ado-connection-to-xlsx-file#18144945 – jsotola Jul 21 '17 at 08:50
  • @jsotola Thank you for the response. I don't think connection string has a problem as I am able to connect but somehow the table doesn't seem to be read properly. What I expect is that I might be missing a parameter which I need to specify. Notice that I am using "Microsoft.Jet.OLEDB.4.0" and not the other provider "Microsoft.ACE.OLEDB.12.0". – Junaid Arshad Jul 22 '17 at 04:54
  • i faintly remember that i had something like this years ago. it was some parameter like HDR. i remember this site https://www.connectionstrings.com/excel/ – jsotola Jul 22 '17 at 07:37
  • it may have been this `Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties="Excel 12.0;HDR=YES";"` – jsotola Jul 22 '17 at 07:57
  • or this ... the xml keyword rings a bell `"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""` – jsotola Jul 22 '17 at 08:03

1 Answers1

0

Connecting directly to sharepoint server to access a file creates a lot of problems.

For Example

  • 1 of the issue is already described above;
  • 2 issue is that you would have to manually connect to sharepoint everytime the computer starts before the program can read the file.

A better way of connection can be found in the following answer:

Get the content of a sharepoint folder with Excel VBA

Junaid Arshad
  • 1
  • 3