On 8/15/2016 11:18 AM, Dave Crozier wrote:
I have been forced to bite the bullet on a small project and use embedded OLE Excel objects as general fields in a VFP table.
...
Ideally I want to query the embedded Excel and pull back a named range of cells. Into VFP. Any ideas? Dave
Others have touched on pieces of what I'd do, but here is the overall "design" concept: 1) Store the Excel file in a Memo field (I do "Memo binary"); you can get a file to a string to store via filetostr() 2) When you need data out: a) save the contents of the memo field to a file - aka strtofile() (temp file, whatever, recommend making the extension correct - .xlsx) b) use Excel automation to open the file and do the usual data extraction via automation c) close the automation object and delete the file
Notes about this: 1) storing the file (filetostr()) and pulling it out and saving it to access is VERY fast (VFP functions) 2) Excel automation is slow (as you're probably aware) - creating the excel object (aka oExcel = CREATEOBJECT('excel.application')), and pulling data through multiple calls to get cells values 3) if you have to read, modify, and restore file data into the VFP table you'll have to occasionally PACK because of bloat. 4) filesize usually isn't too much of a problem - Excel spreadsheets can be huge, but you can do some quick calcs to figure that out. With the 2GB limit on Memo files (.FPT) I've rarely hit a problem (when I did I simply split the data into multiple tables via a VFP storedproc based on filename, etc - and had a VFP storedproc decipher which table to go get data from - I had something like 40GB of stuff like this in one app).
If you have a LOT of data you need to pull out of the Excel file, it may be better to grab it and save it in a separate table while your are storing the file in the memo field. You could even use automation to copy the cell-range to it's own SS tab, then save that tab as a .CSV, and then import that into your own table (I've not had great success with EXCEL XML being perfectly readable in VFP). So if you've got like 10,000 rows of data, that may be a better way. Then in the table that has the data you can have the foreign key back to the table that has the actual SS in a memo field.
I'm suggesting all this because it seems you need to be able to get to that original spreadsheet - the real thing - at any given time. And if you store the filename you can essentially recreate it whenever you want. But it also seems you want data from that SS for your app or whatever. So the automation approach or the data extract while saving approach will work for that. Of course this assumes the SS isn't constantly changing, or that your app is not changing the data in the SS, etc; that would add a little more complexity, but still be quite feasible.
HTH, -Charlie