If I create an .xls file (Excel 8.0) I am able to create a table (=sheet) with numerical fields:
(setq oConnect (vlax-get-or-create-object "ADODB.Connection"))
(vlax-invoke
oConnect
'Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test_8.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\";"
)
(vlax-invoke oConnect 'Execute "CREATE TABLE [newTable] (Nr INT, Article VARCHAR, Price INT)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (1, 'Bolt', 0.25)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (2, 'Nut', 0.15)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (3, 'Washer', 0.10)")
(vlax-invoke oConnect 'Close)
If I try the same with an .xlsx file it does not work. All the fields in the new table are text fields. Does anybody know a solution?
(setq oConnect (vlax-get-or-create-object "ADODB.Connection"))
(vlax-invoke
oConnect
'Open
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test_12.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;\";"
)
(vlax-invoke oConnect 'Execute "CREATE TABLE [newTable] (Nr INT, Article VARCHAR, Price INT)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (1, 'Bolt', 0.25)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (2, 'Nut', 0.15)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (3, 'Washer', 0.10)")
(vlax-invoke oConnect 'Close)
I have tried alternative field types (INTEGER, REAL, DOUBLE). They do not solve the issue. Although they also work for Excel 8.0.