[Jprogramming] Write array to Excel via OLE Automation without using Paste?

[David Mitchell]

On 9/28/2010 16:35, Cox, Ed wrote:
> I successfully wrote a tool for running SQL queries on a server and dumping the results into Excel (2007) spreadsheet using OLE Automation.  It works great except for one thing...the xlutil.ijs script implements the xlwriter verb to paste the data into the spreadsheet.  Thus a conflict occurs if I want to use copy and paste on my PC while the tool is running.
>
> In studying OLE Automation I found an example of programming via C# that appears to write an array directly to a spreadsheet (see code fragment below):
>           // Create an array to multiple values at once.
>                   string[,] saNames = new string[5,2];
>
>                   saNames[ 0, 0] = "John";
>                   saNames[ 0, 1] = "Smith";
>                   saNames[ 1, 0] = "Tom";
>                   saNames[ 1, 1] = "Brown";
>                   saNames[ 2, 0] = "Sue";
>                   saNames[ 2, 1] = "Thomas";
>                   saNames[ 3, 0] = "Jane";
>                   saNames[ 3, 1] = "Jones";
>                   saNames[ 4, 0] = "Adam";
>                   saNames[ 4, 1] = "Johnson";
>
>          //Fill A2:B6 with an array of values (First and Last Names).
>                   oSheet.get_Range("A2", "B6").Value2 = saNames;
>
> So my question is, does anyone know if this approach works in J?  Or is there some reason that we are forced to use paste from the clipboard?
>
> Ed Cox
> Principal Data Analyst
> MedMined(tm) Services
>
>
>
> _________________________________________________
>
> This message is for the designated recipient only and may contain privileged, proprietary
> or otherwise private information. If you have received it in error, please notify the sender
> immediately and delete the original. Any other use of the email by you is prohibited.
>
> Dansk - Deutsch - Espanol - Francais - Italiano - Japanese - Nederlands - Norsk - Portuguese
> Svenska: www.carefusion.com/legal/email

Ed,

One possibility would be to rewrite xlutil to use another communication method 
to transfer the data, such as sockets.

Another would be to run J as a server to Excel and have a VBA script in Excel 
start J, run your script and import the data.

--
Cheers,
David Mitchell