>>Thanks to all contributors this great thread.
>>
>>This is of general interest and useful to a lot of people IMHO:)
>>
>>By the way is there a trick to do just the opposite? i-e feed a table-like set of MS-Excel cells as a VFP array in order to avoid cell-per-cell parsing when downloading the content of a heavy sheets via com (I am not discussing ADO or ODBC here).
>
>Depend on a situation you can avoid COM all together. Provided user interaction is allowed.
>I do majority of excel data imports without using com and it is light fast. Here is scenario;
>
>A) User opens file him/herself, select range of excel sheet that he is interested in importing and right click-copy.
>
>B) Dialog form pops up with large edit field. User then presses right click - paste. Desired data are copied into editbox.
>(It does not looks nice but it is all there) And then presses button to confirm import.
>
>C) I get content of a edit box into a variable. Break it down into array of row strings using alines(). Break it further down to array of row cell values (as strings) again using alines(). (Delimited by tab) Then move excel row values into into cursor row values based on position left to right.
>
>D) User closes his excel instance (which was not even referenced by VFP) and continue to work with program.
>
>Advantages;
>- Very high speed. From the moment user confirms import, it essentially all becomes string crunching operation instead of COM data exchange.
>- User directly selects and copy exact section of excel sheet that interests him. (6th sheet, range x,y)
>- You lose all those kinky excel type/content conversion problems as it essentially become conversion of string (known type) into (again) known type.
>- You do not need to keep excel instance attached to your app via variable and thus avoid problems of lingering excel instances in memory.
>
>
>Disadvantages.
>- Requires user interaction.
>- Non fully generic. While you can have generic classes/objects for copy paste phase, this approach requires writing distinct procedure
>(or BO) which moves set of array values into respective cursor or table record/fields.
>(And then perhaps parsing it further via ODBC to populate backend database content)
>
>In my case this dates back all the way back to W98 - XP era, when importing 1000 rows via COM automations was indeed
>nightmare awaiting to happen. Today perhaps they might have better mass import scenarios. I am interested myself if there is anything
>new I could use to improve this.
>
>HTH
>Sergio


For fast bulk import,

- I open the excel via automation
- save the sheet in a tmp file, text format - xlTextWindows
- close Excel
- I can now process the tmp text