View Full Version : Open a worksheet with ADODB doesn't work

07-11-2017, 05:51 AM

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

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

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

' copy recordset to destination listobject
loDestTable.Range(2, 1).CopyFromRecordset adoRs
If Not adoRs Is Nothing Then
If adoRs.State = 1 Then
End If
End If
If Not adoCon Is Nothing Then
If adoCon.State = 1 Then
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")
Set test = Nothing

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

Thanks :)