Dave Crozier wrote on 2016-08-15:

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.

Although totally against my principles where I would normally save the

link to a physical Excel sheet and then open it up as a com object, is there anyone who knows how I can drive the embedded Excel object in the same way that you can when using Automation?

Ideally I want to query the embedded Excel and pull back a named range of

cells. Into VFP.

Any ideas?

Dave

Dave,

So using automation to pull the information into a cursor then using CursorToXml to store the data from the spreadsheet is out of the question?

The only other thing I can suggest is to STRCONV the actual XLS file to a string that you can store in a MEMO field.

Tracy Pearson PowerChurch Software

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