2

I have a problem here with the new Office 2019. The Microsoft help pages have absolutely no relevant information about it. Looks like the same connection string that was used in Office 2010 does not work with 2019 anymore. On machine running the Office 2010 it works perfectly, but on the one running Office 2019 I get run-time error 3704 - operation is not allowed when the object is closed.

The connection string is 

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Any ideas? I suspect that the OLEDB provider version might have changed, but can't find anything in this aspect in Microsoft documentation.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
Slava S.
  • 31
  • 1
  • 6
  • 2
    I think JET was deprecated back in 2008, with Office 2010 being the last version that was compatible. Check out `Microsoft.ACE.OLEDB.12.0` as the natural successor. – CLR Dec 13 '18 at 15:56
  • Was just about to say as @CLR. This site might be worth having a look at: https://www.connectionstrings.com/access/ – Darren Bartrup-Cook Dec 13 '18 at 15:57
  • I would use an Ace driver Connection String from [ConnectionStrings.com](https://www.connectionstrings.com/ace-oledb-12-0/) – TinMan Dec 13 '18 at 16:00
  • 1
    @CLR For Excel 2019 `Microsoft.ACE.OLEDB.15.0` will do the work also :) Not sure if higher number exists... – JohnyL Dec 13 '18 at 19:20
  • Thank you everyone! After reading through the connectionstrings.com I changed the driver to ACE.OLEDB.12.0 and also update the Excel version in Extended Properties to 12.0. It works now well for both Office 2010 and Office 2019 – Slava S. Dec 14 '18 at 16:06

0 Answers0