Microsoft.ACE.OLEDB.12.0 doesn't want to read my "live" .xlsb datas, only the last saved one, to the countrary of Microsoft.Jet.OLEDB.4.0 .... is this a bug ?

Question

Hello,

for practical reasons, I'm using sql to read and transform datas in an excel sheet, from the same excel sheet.

Everything was nice using "Microsoft.Jet.OLEDB.4.0", but with "Microsoft.ACE.OLEDB.12.0", I have to register my current excel sheet for it to read the last version of the datas. that is unexpected.

==> Is it a Bug ? Has anyone a solution ?

sample : create workbook "titi.xlsb". open it. create a sheet "hello" and type in cell a1, "World". save it, change A1 per "Goodbye". do NOT save

then trying "select * from [Hello$]" with a macro on this very same workbook :

- I read "World" (last saved data) using connexion string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=hello.xlsb;Extended Properties="Excel 12.0;HDR=No;IMEX=1", .

- I read "Goodbye" (live data) using connexion string Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hello.xlsb;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

Answer 1

Hi Stone,

Could you describe more detailed for “change A1 per "Goodbye". do NOT save”? Does it mean you change the A1 but not save the file? If it is yes, I can reproduce the scenario but I can select the “live” data.

If you change A1 from “world” to “goodbye”, and press “Enter”, you can select the latest data, but if you didn’t press “Enter”, you will always select “world” from the sheet.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.