Open a worksheet with ADODB doesn't work

[aua]

Hello,

I try to open an excelfile via ADODB which is located at our SharePoint Server. The problem is that I've to open the excelfile (once) manually (browse to the site, open and close it).
After I opened/closed the excelfile I can use my ADODB code. Other excelfiles (also at the SharPoint server) works without any Problems.

I noticed the problematic excelfile throw a message (when opening) regarding to "check out the file" (but I won't do that).

I also tried to change the connectionstring from "Excel 12.0 XML" to "Excel 12.0 Macro" - it's the same result.

My code is:


Dim adoCon As Object ' ADODB Connectionobject
Dim adoRs As Object ' ADODB Recordsetobject
Dim sConString As String ' Connectionstring
Dim sQuery As String ' querystring
Dim sSheetName As String ' worksheetname
Dim sFileName As String ' Filename
sFileName = "\\sharepointserver.tld\site\excel.xlsx"
' create adodb object and connectionstring
Set adoCon = CreateObject("ADODB.Connection")
sConString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Xml;IMEX=1;HDR=YES';Data Source=" & sFileName

' open connection
With adoCon
.ConnectionTimeout = 180
.Open sConString <- produce the error
End With

Set adoRs = CreateObject("ADODB.Recordset")
adoRs = adoVerbindung.OpenSchema(20) ' 20 = adSchemaTables
sSheetName = adoRs!TABLE_NAME ' use first sheetname
adoRs.Close

sQuery = "Select COL1,COL2 from [" & sSheetName & "]"

' fill the recordset...
With adoRs
.CursorLocation = 3 ' adUseClient
.CursorType = 3 ' adOpenStatic
.LockType = 1 ' adLockReadOnly
.Source = sQuery
.ActiveConnection = adoCon
.Open
End With

' copy recordset to destination listobject
loDestTable.Range(2, 1).CopyFromRecordset adoRs
If Not adoRs Is Nothing Then
If adoRs.State = 1 Then
adoRs.Close
End If
End If
If Not adoCon Is Nothing Then
If adoCon.State = 1 Then
adoCon.Close
End If
End If
Set adoRs = Nothing
Set adoCon = Nothing


The error appears then I open the Connection (not at the recordset). The error is an "unknow/application error".
I also added an open/close before I run the ADODB code - the same.


Set test = Application.Workbooks.Open("inetproto://sharepointserver.tld/site/excel.xlsx")
test.Close
Set test = Nothing


Has anybody a clue or there are any specific flags to open such file?

Thanks :)

Thomas