>>>>>It will be (somewhat) fancy-pants and I've been working with Greg Green's VFPxWorkbookXLSX class:
https://github.com/ggreen86/XLSX-Workbook-Class>>>
>>>Right. I can probably save you some time: we tried creating an xls sheet with [PLACEHOLDER] values in key cells, hoping we could just STRTRAN() values into the FILETOSTR() content for a magically updated Excel sheet- but doesn't work if the replacement isn't the same size as the replaced text, or possibly not even then. I suppose we might have considered very large [PLACEHOLDER] and padded replacement to size. If you have a few moments, might be worth an experiment depending how many cells you want to populate...
>>
>>Once upon a time I managed to do a lot using Marc Grajower's approach (well, he was my boss at the time :), to use named ranges in an excel sheet (which would serve as a template), then either stuff values in them or do a range.select(), then .selection.paste() - and then paste the contents of what you get with vfp.datatoclip() - possibly losing the header row first. The named ranges gave us the ability to hit any cell with a value without exactly knowing its row and column - these could change at any time, columns were specially prone to insertion or move. The range where multiple rows were pasted would get a sufficient number of rows inserted first. This worked like a charm - the big and bulky insertion of multiple rows was done in about two microjiffies, and the precise filling of various cells in the header was done without needing to know their coordinates.
>
>Interesting - I'll have to ponder how I might make use of that :)
Found the relevant bit of code... there's more in there, about preserving any formulae which were in the first row of the range and restoring them afterwards, don't remember why we did this. And I hope Marc won't mind, I think he's not using this anymore and it's from 2005 anyway...
c=_Cliptext
_vfp.DataToClip(,,3)
N=Set("Memowidth")
Set Memowidth To 1023
_Cliptext=Strtran(_Cliptext, Mline(_Cliptext,1)+hCR,"")
Set Memowidth To N
oXls= This.oApp
oRng1 = oXls.Range(cRange)
nRows= Reccount(cCRS)
If Reccount(cCRS) > 1
oXls.Application.CutCopyMode = .T.
oRng = oXls.Range(oXls.Cells(oRng1.Row + 1, 1), oXls.Cells(oRng1.Row + nRows - 1, 1))
oRng.EntireRow.Insert
oRng = Null
Endif
Dimension a[orng1.Columns.Count]
For i=1 To oRng1.Columns.Count
o= oRng1.Cells(1,i)
If o.HasFormula
a[i]= o.formulaR1C1
Endif
Next
oCell=oRng1.cells(1,1)
oCell.Select()
oXls.Selection.PasteSpecial()
As for named ranges, assume you have a range's name in lcRange, then
oExcel.range(lcRange)=luValue
is all you need. The names of the ranges are your connection, and they persist no matter how you move the cells around, which is nice.