Hi all,
I have been trying to figure out what is the best way to export data into Excel from an ABL program using OLE automation (in OE 10.2B).
Using direct references to cell values is quite slow with large amounts of data:
chExcel:Range("A1"):VALUE = "x".
chExcel:Range("A2"):VALUE = "y".
...
Using clipboard (write data into clipboard and then paste it into Excel) is much faster, but has its problems (data has to be written in chunks because of limited clipboard size and you cannot be 100% sure that another application is not using clipboard at the same time).
Recently, I found out that in VBA it should be possible to do it with an array object, something like this:
//declare an object array
object[,] objData = new Object[10, 10];
//Copy data into array
objData[0, 0] = "x";
objData[0, 1] = "y";
// Insert array into Range
objRange.Value2 = objData;
I tried the similar approach in ABL using a two dimensional .NET array:
DEFINE VARIABLE ss AS "System.String[,]" NO-UNDO.
ss = NEW "System.String[,]" (10, 10).
ss:SetValue("x", 0, 0).
ss:SetValue("y", 0, 1).
chExcel:Range("A1:B1"):VALUE = ss.
This did not work, the last line gives an error message: Error occurred while accessing component property/method: VALUE. The parameter is incorrect.
However, I found out that you can use an extent variable to export one row at a time (I don't know if this a supported method):
DEFINE VARIABLE cArray AS CHAR EXTENT 20.
cArray[1] = "x".
cArray[2] = "y".
chExcel:Range("A1:B1"):VALUE = cArray.
I have been trying to figure out what is the best way to export data into Excel from an ABL program using OLE automation (in OE 10.2B).
COM-HANDLE and .NET are not compabile.
But you can use Excel using the Interop Assemblies (http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx) and the GUI for .NET technology (with the Exception of handling events).
Thanks, Mike, for a quick response. I can now stop trying to do the impossible...
I will try the Interop Assemblies, thanks for a hint.