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
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
