473,222 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,222 software developers and data experts.

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 8559
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rob McLennan - ZETLAND | last post by:
Hi, I have set up an external stylesheet, named "print.css", to format the style of all pages printed from my company's website. I've been previewing my changes to the stylesheet by doing...
2
by: nivas.meda | last post by:
Hi, I have an excel sheet with a graph and cells.If i change the value in the excel cells the graph will reflect.Now i am going to implement this functionality in html page.I successfully saved...
5
by: nivas.meda | last post by:
Hi, I have an excel sheet with a graph and cells.If i change the value in the excel cells the graph will reflect.Now i am going to implement this functionality in html page.I successfully saved...
0
by: WStoreyII | last post by:
Hello, I was wondering if there was a way to open a spread sheet up in a windows mdi form. I have tried the spreadsheet control but that does not work because i wish to open up an exisiting...
2
by: RICHARD BROMBERG | last post by:
I wrote a small Access application that accepts a City Name and a Street Name and runs a Query based on them . I want to create an Excel Spread sheet that contains all the matches found by the...
8
by: pamelafluente | last post by:
Hi guys, Is it possible to add "onload" (via Javascript) a new class to the <styleheader section? If yes, how would that be done ? <style type="text/css" media="screen"> .NewStyleClass{...
5
by: stuart79 | last post by:
I am looking for a code that enables me to open another excel spread sheet while i am in my XL form i have this code - Workbooks.Open Filename:= "OtherFilename.xls" - which opens up the XL spread...
2
by: glibo | last post by:
I am trying to change the tab name when i open a excell spread sheet on the run time in c#. I changed it in the code: Response.AddHeader("content-disposition","filename=" + sFileName + ".xls");...
3
by: akristensen | last post by:
I am new to this site, so be patient if I do not ask the question correctly. Current Target Platform: Browser: MS IE, script language: Javascript (will use VBScript, but JS is preferred), External...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.