Streaming data to Excel ?

[kopite]

Is it possible to copy data out of Javascript (or IE's JS version) to Excel worksheet ?

I am able to copy to the clipboard so was wondering how to get that into Excel - any suggestions ?

Cheers!

[khaki]

Hi kopite...

you probably need to explain better what it is that you are exactly trying to do.

javascript cannot "write" to Excel... so what do you mean by "copy data out of javascript"?
what data does the javascript contain?
a value from a variable?

please provide a bit more info.
;) k

[kopite]

Hi,

Basically, I want variable data held in by the JS to be sent to Excel, in the same way that it can be done by copying to the clipboard.

e.g. clipboard....

var toCopy = "my var data to copy into excel but for this case to the clipboard"

window.clipboardData.setData('Text', toCopy)

[khaki]

oh...

i don't know about the clipboard...

but javascript cannot write directly to a database (that requires server-side technology like ASP) or named ranges in Excel...

whether javascript can write to the clipboard is nothing that i have had the need to do... although if it is possible, it would still require that a manual "paste" be performed in Excel (or it could be done programmatically, i suppose... but with little forsee-able advantage over doing so manually. "paste" vs "run" ... and "run" would be set up to simply just "paste" :rolleyes: ).

do you have ASP capability?
if so... you can submit the contents of a form into Access (and Excel can be linked to the Access data).
So instead of a javascript variable that gets copied/pasted to a clipboard, a form can submit the same data/info to a db... where it can be used in any number of ways (including programmatically with Excel).

anyway... maybe your after something else entirely :rolleyes: ... (like "the clipboard")... so i guess i can't help you there.

just giving you other options/ideas... that's all :rolleyes: ...
;) k

[AdamGundry]

Does this code have to be embedded in a webpage? Javascript can write to Excel, but only if it is run from a .JS file (not in a web browser). This would require users to download and execute the script.

If you use Windows, there are examples of Excel control in Javascript here (I assume, they are there in Win 98):
C:\Windows\Samples\Wsh

Adam

[DrDaMour]

javascript can control windows, but cannot control windows applications. Copying to a clipboard is a windows function but copying from teh clipboard to a program is handled by the program, so javascript can't direct that. As mentioned you either need to use some server side code, or you can copy the code to clipbaord and tell the user to paste it themselves. You are limited.

[kopite]

Ok thanks.

This is for client side only - so I guess the users will have to paste from the clipboard.

[Jona]

Actually this can be done using ActiveX. Here is an example:

var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;
ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1";
ExcelSheet.SaveAs("C:\\TEST.XLS");
ExcelSheet.Application.Quit();

But the user will have to agree to allow ActiveX usage.

[khaki]

wow!
javascript is looking a whole lot different all of a sudden!
it looks... hmmm... somewhat familiar... but i can't put my finger on it...
what could it be?

ah... yes!
VBA!!!
:)

so... that is an IE-only solution... but if that does not matter... then it is a solution nonetheless.

however... kopite says that he has a javascript variable.
so... i suppose that he could populate a hidden text box and switch the variable between languages... but it's still an IE-only solution (but a solution nonetheless ;) ).

not sure that's what kopite is after... but... any port in a storm?
dunno!
;) k

(ps - var ? ! ? )

[Jona]

(ps - var ? ! ? )

If you were talking to me, and are referring to "var ExcelSheet" in my script, is there something wrong with it? I know I couldn've cleaned up the code a little, but that was an example.

It's an IE only solution, true, and as you said, nevertheless it's a solution and the only one I could think of that used JavaScript (without having to be serverside).

Yup, Microsoft does everything that way... Looks just like VBA!

[DrDaMour]

well there IS always that way.... activeX is CHEATING :D

[Jona]

Originally posted by DrDaMour
well there IS always that way.... activeX is CHEATING :D

Hehe... Aren't I sneaky? :p J/K

[kopite]

I have just tried it and it works, only problem is, is that Excel needs to already be open.

Is there another method that can open Excel and/or check whether Excel is already open ??

Thanks!

[Jona]

Hmmm..... I'm not sure about that one. I can write to, read, extract data from, files, but opening programs? This has been a question among many Web developers. First, I'd say that you can try opening the ExcelSheet from the Web page:

<a href="file:///c|/your_excelSheet.xls">open</a>

But this will open Excel in the current window. So you could do:

<a href="file:///c|/your_excelSheet.xls" target="_blank">open</a>

Would that work for ya?

[khaki]

If you were talking to me, and are referring to "var ExcelSheet" in my script, is there something wrong with it? I know I couldn've cleaned up the code a little, but that was an example.the var thing?

var is javascript

dim is vb/vba/vbscript

that's all sweetie ;)

oh... and kopite...
here is the exact code that Jona has referenced:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctcreateobject.asp
it's vb (vba/vbscript/whatever)... so IE-only (although it doesn't seem to be an issue for you).

you can probably answer many of your next questions simply by reading-through that link for yourself.

good luck and keep us informed...
(but... it's IE-only... tsk-tsk :rolleyes: )
;) k

[Jona]

Yah, I know that, Khaki, I just didn't know what you meant by it. Comparisons... :eek:

Actually, that wasn't even on my mind when I posted the script. But, you did find a VBScript way of it. I guess either JS or VB works fine... Both IE compliant.. :) Oh, BTW, I'm going to make a VB game called Super Strike: Demolition. Just FYI.. :cool: Oops! I forgot... Hehe, this isn't game dev! :rolleyes:

(Edit) BTW, I found an exact replica of the script at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/js56jslrfjscriptfunctionstoc.asp but I didn't get it from there. Sorry for the deception, if it looked like I wrote that script. :o I got it from another reference. But now that I know that MSDN actually has that reference... (Wait, strike that, I knew it had the reference but I didn't know it was the same reference, and the first time [add an "s" to the end of that word and multiply it by 10] I went there I couldn't figure it out. It's so confusing! :confused: )

[kopite]

Hi,

I can open Excel if I put this in before anything else:

var ExcelSheetApp = new ActiveXObject("Excel.Application");

..then

var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;
ExcelSheet.ActiveSheet.Cells(1,1).Value = "Hello!";

...but, I do not want it to Quit, it closes itself within seconds of going out of scope. I would rather that the Application and Sheet remained active until the user has decided otherwise.

How can I stop closure?

Cheers!

[DrDaMour]

keep it in scope, don't put it in a function.

[kopite]

Excellent, I have now declared the variables in the js file itself, as opposed to in the function, to keep it within scope.

e.g.

var ExcelSheetApp;
var ExcelSheet;

function copyToExcel()
{
ExcelSheetApp = new ActiveXObject("Excel.Application");
ExcelSheet = new ActiveXObject("Excel.Sheet");

ExcelSheet.Application.Visible = true;
ExcelSheet.ActiveSheet.Cells(1,1).Value = "thanks!";
}