Excel crashes on ADODB connection open command

2
votes

I'm trying to use vba in Excel (2013) to use ADODB connection and SQL to query and collate data from another tab. My laptop is running Windows 10 Pro 64-bit(10.0.17763) and Excel Standard 2013 64-bit (15.0.5233.1000)

I have tried removing and re-adding my Active X References, but has not solved the issue. I've also tried removing all possible references and re-adding them one by one as well as trying to use older versions. All result in the same result. My references chosen are: Visual Basic for Applications, Microsoft Excel 15.0 Object Library, OLE Automation, Microsoft Office 15.0 Object Library, Microsoft Forms 2.0 Object Library, Microsoft ActiveX Data Objects 6.1 Library, Microsoft ActiveX Data Objects Recordset 6.0 Library.

When I get to the cnExcel.Open in the below code, excel just crashes(i.e. closes) with no error messages.

Dim cnExcel As Connection
Dim rsExcel As Recordset
   .....
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Users\trheinsc\Desktop\InProgress\Error Dashboard_TEMPLATE.xlsm"";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cnExcel = CreateObject("ADODB.Connection")
Set rsExcel = CreateObject("ADODB.Recordset")
rsExcel.CursorLocation = adUseClient
cnExcel.Open strCon                     ' <=  This is where Excel blows up and shuts down.

' Populate Iteration Columns
sqlSelect = "SELECT DISTINCT [Iteration]"
sqlFrom = "FROM [ErrorDetail$]"
sqlWhere = "WHERE [Iteration] Is Not NULL"
sqlGroup = ""
sqlOrder = "ORDER BY [Iteration] DESC"
sqlString = sqlSelect & " " & sqlFrom & " " & sqlWhere & " " & sqlGroup & " " & sqlOrder
rsExcel.Open sqlString, cnExcel
cDashRow = 1
cDashCol = FirstIterCol
Do
    If rsExcel.EOF Then Exit Do
    shDashboard.Cells(cDashRow, cDashCol) = "Iter " & rsExcel(0) & Chr(10) & "# Errors"
    shDashboard.Cells(cDashRow, cDashCol).Columns.AutoFit
    aIterArray(cDashCol) = rsExcel(0)
    cDashCol = cDashCol + 1
    rsExcel.MoveNext
Loop Until rsExcel.EOF
LastIterCol = cDashCol - 1
If rsExcel.State = 1 Then rsExcel.Close

Any assistance would be greatly appreciated.

3
sqlexcelvbaactivexadodb
I don't think you need those double quotes " around the data-source, only around the extended properties. Also the extended properties should read Excel 12.0 Xml; if I'm not mistaken. – Kostas K.
"Excel 12.0 Xml" is only for non-macro file types and will not work in OPs situation. – timlash
@timlash Thanks, didn't know that. – Kostas K.
@Kostas K. - np. Your observation on double quotes is interesting, but I would have done the same since there is a space in the file name, especially if I couldn't just rename the file. I avoid file names with spaces like the plague since it's hard to predict how different program parsers will handle them. – timlash
@timlash Those connection strings give me headaches. I've never opened an .xlsm through ADO, only .xlsx, but I still don't understand why the need to differentiate between Excel 12.0 Marco and Excel 12.0 Xml. What's the drive behind this? Does it suppress the macros? I don't think so. – Kostas K.

3 Answers

2
votes

Clearly a connection string problem (isn't it always?). When connecting to an XLSM file in Excel 2013 the Extended Properties needs to include "Excel 12.0 Marco", not "Excel 12.0" as you have it.

1
votes

I use it like this.

Dim Rs As Object
Dim strConn As String
Dim i As Integer

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 12.0;"


Set Rs = CreateObject("ADODB.Recordset")
Rs.Open strSQL, strConn
1
votes

After much extensive searching for a solution to get past this issue, i found that recommendations on other providers to do the connection. The original (1st) and 2nd using JET didn't work, but the 3rd using MSDASQL did work.

So i left all 3 types of calls in my code and just commented out the ones that didn't work. 

strFile = ThisWorkbook.FullName
'Using Microsoft.ACE.OLEDB Provider - If you get an issue with ACE OLEDB Provider try JET Provider
'strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

'Using Microsoft.Jet.OLEDB Provider - If you get an issue with Jet OLEDB Provider try MSDASQL Provider (above statement)
'strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

'Using MSDASQL Provider
strCon = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strFile & ";HDR=Yes';"