I hope everyone is well and hope someone might be able to suggest how to resolve the below issue...
My Excel 2010 crashes when attempting to open ADODB.Connection (establishing connection to later use recordset to read contents of another sheet within the same Excel file).
Everything was working absolutely fine until few weeks ago with the issue first observed shortly after Skype for Business was distributed/installed on our PCs (or possibly due to some other Microsoft system update applied at the same time).
Below is a short/relevant code excerpt:
Dim cn As ADODB.Connection
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon ' - the code crashes here every time - without fail...
I'm running Excel Office 2010 (32bit) on Win7 Enterprise (64bit) system (it is on an enterprise network so can't really do anything about the Office/Win versions).
Would anyone happen to know or have any ideas as to how to overcome the problem?
Thank you all so kindly for any suggestions you may throw my way. Your help is greatly appreciated.
I managed to overcome the problem by replacing the initial connection string as per below:
GetConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Not sure whether this is the best/most effective data connection available within my current environment (Office 2010 (32-bit) running on Win7 Enterprise (64bit) system) - but it works! :-)