Using the ACE OleDB Driver to connect to Excel from VFP.

[Alan Bourke]

This is the driver installed via:
https://www.microsoft.com/en-us/download/details.aspx?id=54920,
Microsoft Access Database Engine 2016 Redistributable.
It installs the ACE 12 OLEDB and ODBC drivers so you can (for example)
query Excel sheets using SQL into a VFP cursor. I've been using the 2010
version for years.
This version isn't playing ball with VFP however. It appears to be
installed correctly because I can pull data out of a sheet from a C#
program. However trying to do the same from VFP, with the exact same
connection string throws up a dialog looking for a data source to
specified, and if you cancel that the SQLCONNECT() fails.
Here's the connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=excel.xlsx;Extended
Properties="Excel 12.0;HDR=YES;"
Has anyone used this version successfully from VFP using the
OLEDB version?




--
Alan Bourke
alanpbourke (at) fastmail (dot) fm




--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

Post by Alan Bourke
program. However trying to do the same from VFP, with the exact same
connection string throws up a dialog looking for a data source to
specified, and if you cancel that the SQLCONNECT() fails.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=excel.xlsx;Extended
Properties="Excel 12.0;HDR=YES;"
Has anyone used this version successfully from VFP using the
OLEDB version?

If you are connecting to Microsoft Office Excel data, add the
appropriate Extended Properties of the OLEDB connection string based
on the Excel file type:

File Type (extension)
Extended Properties
---------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls) "Excel 8.0"
Excel Workbook (.xlsx) "Excel 12.0 Xml"
Excel Macro-enabled workbook (.xlsm) "Excel 12.0 Macro"
Excel Non-XML binary workbook (.xlsb) "Excel 12.0"

Perhaps you need to add "Xml" to the extended properties setting?
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4tOr=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

Thanks Ted, it doesn't make any difference unfortunately.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

How 'bout the bitness? I've gotten bitten, more than once, because the
32-bit VFP was using 32-bit ODBC but I used the wrong ODBC
Administrator to set up the DSN, or was trying to talk to a 64-bit app
on the other end.

Post by Alan Bourke
Thanks Ted, it doesn't make any difference unfortunately.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4tV4oAKBKNLOnPxs22Bqx2n=Ts7d6x9CTgzfLkfYp-***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

It's 32-bit Office and the 32-bit drivers. The C# tester which works is compiled to 32-bit too.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

https://www.connectionstrings.com/excel/

This gives a lot of the necessary settings for your version of office
product as well as intended data you are pulling.

Post by Ted Roche
How 'bout the bitness? I've gotten bitten, more than once, because the
32-bit VFP was using 32-bit ODBC but I used the wrong ODBC
Administrator to set up the DSN, or was trying to talk to a 64-bit app
on the other end.

Post by Alan Bourke
Thanks Ted, it doesn't make any difference unfortunately.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJgwWdfKz_hxzuRcpUri04LEqjNx1=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.