>>>Hi,
>>>
>>>An automation process that used to work is now failing, in Excel 2010, for security reasons. Apparently Excel finds the idea of being automated to open an Excel file suspicious.
>>
>>Well, if it allows automation at all, my favorite method of populating a sheet is to do
>>
>>
>>_vfp.datatoclip(,,3)
>>oExcel.Paste()
>>
>>
>>Possibly removing the first line from the _cliptext, as it contains the field names, and/or selecting a cell other than (1,1) to paste to. This is rather fast - I remember I used that in 2005 to churn about 20 megabytes of excel sheets in a couple of minutes.
>
>Thanks. That method is great, and it is what I'll be using. There are, however, some problems with it.
>
>(1) If Visual FoxPro and Excel (via the Control Panel) have different date formats, the dates will be inserted incorrectly. That's not a problem in my case, since I already had SET SYSFORMATS ON in any case.
The same problem occurs with exporting to / importing from .csv files. A thing to watch at all times.
>(2) For memo fields, the text "memo" gets inserted into Excel. The cursor to be copied can be prepared in advance (by converting the memo to character), but - in my case - since this is used for a generic report, possibly for hundreds of reports, it may require a lot of individual adjustments.
Most of the methods have trouble with memo fields. I generally either did what you say, or filled memo fields via automation after pasting.
>(3) Unlike the COPY TO ... XLS method, the column widths will not adjust to the size of the field.
I generally did this when pasting into a predefined template sheet, so I already had a whole layout prepared, with named ranges (thanks to Marc Grajower, who did the groundwork in that area). Some code was required to expand the range where the pasting occurred - had to insert additional rows beforehand - but after that everything would click in place, even the formulae with totals beneath the range would still be correct.