//z 2012-08-13 17:53:02 IS2120@csdn.T1380725028[T91,L1441,R37,V860]
Well I wanted to set up what I thought would be a simple Excel file import into my ProxyChecker, so I used previous code to upload the file to the web server and create a OLEDB connection to it.
My Excel 8 (Excel 2003) connection string was:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ServerFullPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
And my Excel 12 (Excel 2007) connection string was;
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ServerFullPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
But when I went to run, both XLS and XLSX connections would fail with either:
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
or
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
//z 2012-08-13 17:53:02 IS2120@csdn.T1380725028[T91,L1441,R37,V860]
I sourced the problem to the fact that the connection strings I was trying to use 32-bit drivers, but I was on a 64-bit Sharepoint installation. After a bit of research I found that it doesn’t appear there were ever any 64-bit Office 2007 drivers, but luckily there are some for 2010, and they appeared to be backward compatible.
So after downloading them from here I went to install them only to be faced with an error saying they couldn’t be installed because I had pre-installed 32-bit office applications. Luckily it told me what they were (as I would have not remembered) – you may have guessed – it was Sharepoint Designer 2007.
After the uninstall the AccessDatabaseEngine_X64.exe installation went with out a hitch. So I change my XLSX connection string to Microsoft.ACE.OLEDB.12.0 and BOOM – new error;
The 'Microsoft.ACE.OLEDB.14.0' provider is not registered on the local machine.
Best laid plans of mice and men….
I checked the driver version in ODBC and yep – definitely version 14. Then by complete luck I can across a mention that the version number hadnt been changed in the connection string (sounds like an omission to me) – so I put it back to Microsoft.ACE.OLEDB.12.0 – and ZOOM – everything works.
So there you go. If you are trying to create a connection to an XLSX (Excel 2007) spreadsheet, ensure you have no 32-bit Office apps on the server, install AccessDatabaseEngine_X64.exe and set your connection string to;
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ServerFullPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
The good news is that this driver also works for XLS (Excel 2003) by simply changing the Excel version reference;
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ServerFullPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";//z 2012-08-13 17:53:02 IS2120@csdn.T1380725028[T91,L1441,R37,V860]