I can't use a table and save to excel because there are too many columns and the nature of the data would make it difficult to build several tables and link them together. I am using the with clause extensively but it is the oXLWorkSheet.cells(x,y).value='...' that seems to eat the time.


>>Is there a quicker way to populate cells in Excell other than setting the value of each cell independently? I am loading an Excel Worksheet with up to 1000 columns and about the same number of rows and going the the loop with single cell calls takes forever. Perhaps there is a way with arrays?
>>
>>Thanks!
>>Troy
>
>Your delay is due to the number of calls to the ole object. You might gain some speed using the WITH command to eliminate the overhead of returning the OLE object reference with each call.
>
>The best solution is to save the data to a table (either DBF or XLS). Then open the table from excel via OLE automation from VFP. You can then work with formating the spreadsheet such as adding headers or cut and paste the data to a template.
>
>If you search the UT there have been examples of using the clipboard to paste data into Excel as well. The main problem with this approach is hitting the clipboard's memory limit. If you can't find the thread(s) then drop me a note. I think I have a code example saved somewhere.