Using ADO from JavaScript in HTML to change a spread sheet

U

UnaCoder

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?
 
J

Josh Sebastian

UnaCoder said:
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?
Click to expand...

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
 
U

UnaCoder

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"
 
J

Jambalaya

UnaCoder said:
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?
Click to expand...

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>
 
U

UnaCoder

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.
 
T

Thomas 'PointedEars' Lahn

UnaCoder said:
How to I translate the workbook/worksheet/column names to SQL?

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

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"
Click to expand...

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
 
U

UnaCoder

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
 
T

Thomas 'PointedEars' Lahn

UnaCoder said:
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.
Click to expand...

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
 
U

UnaCoder

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

VK

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

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.
 
T

Thomas 'PointedEars' Lahn

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:]
Thomas said:
UnaCoder said:
[...] 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.
Click to expand...

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.
Click to expand...

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

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
 
U

UnaCoder

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... ?
 
J

Jonas Raoni

UnaCoder said:
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... ?
Click to expand...

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I want to Display Excel As HTML In js 2
I Need Help with making a function that draws in a canvas using location data. 1
Help please to adjust the size of an excel spread sheet in a web page 2
using axcel data in an asp apge with ado 1
Draw a real-time flot chart by fetching data from database and store it into an array using JavaScri 0
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. 7
javascript and Content-Type 1
how to make a tree with randomly selected html tags from an array in python? 0

Staff online

Members online

Total: 81 (members: 1, guests: 80)
Robots: 221

Forum statistics

Threads
473,992
Messages
2,570,220
Members
46,805
Latest member
ClydeHeld1

Latest Threads

Top