469,289 Members | 2,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,289 developers. It's quick & easy.

Using ADO from JavaScript in HTML to change a spread sheet

Hi, I'm trying to use client side JavaScript from an HTML file to open
and make changes to an Excel SpreadSheet using ADO w/ ODBC. What a
pain it is to do this. Anyways, I am able to open the excel file but I
don't know how to use ADO to work with it's contents. So far I have
the following code that works:

<html>
<script>
window.onload = function () {

var ExcelSheet;
var sConnect = "DRIVER={Microsoft Excel Driver
(*.xls)};DBQ=P:\js-test.xls"
ExcelSheet = new ActiveXObject("ADODB.Connection");
ExcelSheet.Open(sConnect);
}
</script>
<body> =) </body>
</html>

Now that I have the sheet open, how do I manipulate it?

Feb 17 '06 #1
13 8326
UnaCoder wrote:
Hi, I'm trying to use client side JavaScript from an HTML file to open
and make changes to an Excel SpreadSheet using ADO w/ ODBC. [...]

Now that I have the sheet open, how do I manipulate it?


I wrote an article about this for Kuro5hin a while ago.
https://www.kuro5hin.org/story/2005/7/14/13942/7643

Please, don't tell me how evil it is that I used innerHTML; I already
know that. I thought mucking with the DOM would have detracted from the
point of the article.

If you're targeting (only) Internet Explorer, consider using an HTA
(HTML Application).

Josh

Feb 17 '06 #2
How to I translate the workbook/worksheet/column names to SQL?

should it be SELECT (sheetname) FROM (COLUMN) WHERE ... ?

I tried SELECT * FROM A
which caused an error "no such object "A"

Feb 17 '06 #3
UnaCoder wrote:
Hi, I'm trying to use client side JavaScript from an HTML file to open
and make changes to an Excel SpreadSheet using ADO w/ ODBC. What a
pain it is to do this. Anyways, I am able to open the excel file but I
don't know how to use ADO to work with it's contents. So far I have
the following code that works:

<html>
<script>
window.onload = function () {

var ExcelSheet;
var sConnect = "DRIVER={Microsoft Excel Driver
(*.xls)};DBQ=P:\js-test.xls"
ExcelSheet = new ActiveXObject("ADODB.Connection");
ExcelSheet.Open(sConnect);
}
</script>
<body> =) </body>
</html>

Now that I have the sheet open, how do I manipulate it?


You don't have the Excel worksheet open. You have opened a connection
to it with your ADODB Connection object against which you can execute
SQL queries, etc. If you want to be able to get a reference to an excel
worksheet object and use its methods that would be something more like
this:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Referencing Excel</title>
<script type="text/javascript">
var red = 3;
var excelobj = new ActiveXObject('Excel.Application');
excelobj.Application.Visible = true;

var wbobj = excelobj.Workbooks.Open('P:\js-test.xls');

var worksheet = wbobj.Worksheets.Item(1);

//alert(worksheet.Name);
if (worksheet.Range('A2').Value < 100){
worksheet.Range('A2').Font.ColorIndex = red;
alert('Warning! The number has fallen below 100.');
}
</script>
</head>
<body>
Nothing to see here...
</body>
</html>

Feb 17 '06 #4
Yes I understand that. the Excel.Application ActiveX Object does not
work from JavaScript, but ADO Connections using ODBC do. The method
you described works from JS script but not from JavaScript in HTML.

Feb 17 '06 #5
UnaCoder wrote:
How to I translate the workbook/worksheet/column names to SQL?

should it be SELECT (sheetname) FROM (COLUMN) WHERE ... ?
No, rather vice-versa. And you don't have to use parentheses.
I tried SELECT * FROM A
which caused an error "no such object "A"


The FROM clause specifies the name of the table you want to access in the
SELECT query. There are manuals and even newsgroups dealing especially
with database programming, you know.
PointedEars
Feb 17 '06 #6
I know this question was slightly off topic, since it is more of an ADO
question than a JavaScript question. I can't quite figure out the
syntax for pulling data from a spread sheet using the Excel ODBC drive
(which is the problem now...). You're right though, maybe FROM should
specify the sheet name... My SQL is a bit rusty =D

Feb 17 '06 #7
UnaCoder wrote:
Yes I understand that. the Excel.Application ActiveX Object does not
work from JavaScript, but ADO Connections using ODBC do. The method
you described works from JS script but not from JavaScript in HTML.


This does not have much to do with JavaScript vs. _JScript_. It has
to do with the Application Object Model of the user agents that support
either programming language. And unless you have a plugin, you cannot
extend that object model.
PointedEars
Feb 17 '06 #8
Yeah, what he said =D

Feb 17 '06 #9
So it would be correct to say that "IE does not support the ActiveX
Object Excel.Application" but it does support ADO =D

Feb 17 '06 #10
VK

UnaCoder wrote:
So it would be correct to say that "IE does not support the ActiveX
Object Excel.Application" but it does support ADO =D


Not sure why did you deside so. The very ActiveXObject explanation in
JScript Reference is based on Excel usage:
<http://msdn.microsoft.com/library/en-us/script56/html/9c7bed07-853f-48aa-92db-3131324746ec.asp>
....
ExcelApp = new ActiveXObject("Excel.Application");
.... etc.

There is a known issue with Excel called over ActiveX as it doesn't
dismiss on Application.Quit

So Microsoft suggest as workaround to use undocumented CollectGarbage()
method:

....
ExcelSheet.Application.Quit();
CollectGarbage()
....

Besides that there is no problem whatsoever.

Feb 17 '06 #11
Please learn to quote what you are referring to:
<URL:http://jibbering.com/faq/faq_notes/pots1.html#ps1Post>
<URL:http://www.safalra.com/special/googlegroupsreply/>

[re-included quotation:]

UnaCoder wrote:
Thomas 'PointedEars' Lahn wrote:
UnaCoder wrote:
[...] the Excel.Application ActiveX Object does not work from
JavaScript, but ADO Connections using ODBC do. The method you
described works from JS script but not from JavaScript in HTML.


This does not have much to do with JavaScript vs. _JScript_. It
has to do with the Application Object Model of the user agents
that support either programming language. And unless you have
a plugin, you cannot extend that object model.


So it would be correct to say that "IE does not support the ActiveX
Object Excel.Application" but it does support ADO =D


No, it would not. You said that "the Excel.Application ActiveX Object
does not work from JavaScript". Only Netscape 4.x and Gecko-based
browsers implement JavaScript. IE implements _JScript_.

In the Netscape-for-Windows AOM, and with the ActiveX Plugin in all
Gecko-based UAs, the ActiveXObject object is supported to facilitate
scripting of the Windows Media Player plugin. AFAIK, other ActiveX/COM
objects are not supported there.

If you did not mean JScript (as implemented in IE) by "JS script" (which
I assumed to be a typo or based on a misconception only), what exactly
do you mean by that?
PointedEars
Feb 18 '06 #12
I tried to use this method and recieved an error that the object cannot
be created so I don't know why it was doing that... ?

Feb 22 '06 #13
UnaCoder wrote:
I tried to use this method and recieved an error that the object cannot
be created so I don't know why it was doing that... ?


Take a look if you're calling the right Activex and also check the
security area of your IE, maybe the activex controls are disabled.

Anyway don't use activex inside a browser, they work just on IE (Firefox
seems to work with activex by adding an extension).
--
Jonas Raoni Soares Silva
http://www.jsfromhell.com
Feb 22 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Rob McLennan - ZETLAND | last post: by
8 posts views Thread by pamelafluente | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.