Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Web Programming > JavaScript > Javascript How-To
| Search | Today's Posts | Mark Forums Read
Javascript How-To Ask your "How do I do this with Javascript?" questions here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Javascript How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 6th, 2005, 11:15 AM
Registered User
 
Join Date: Jan 2005
Location: Hartlepool, Cleveland, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bRvO
Default converting excel formula to javascript

I'm trying to convert this Excel formula : =VLOOKUP(A3,sheet2,2) into Javascript . However , I need help as i'm stuck.

I thought it may have been something like this :

var a = getField("A3").value;
var b = document.worksheet("sheet2").value;
var c = getField("2").value;

Vlookup(a,b,c);

however it doesn't work . can anyone help ?

thanks

i don't really know what I'm doing :/
 
Old January 7th, 2005, 04:47 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

What exactly are you trying to do? Are you mimicking Excel in a web page or trying to control Excel from script?


--

Joe (Microsoft MVP - XML)
 
Old January 7th, 2005, 12:51 PM
Registered User
 
Join Date: Jan 2005
Location: Hartlepool, Cleveland, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bRvO
Default

Basically , we have an excel spreadsheet that stores information on sheet2 , on sheet1 if you enter some info into cell A3 it searches the data in sheet2 column A , finds the data and returns the information in in colums B-G of that row ...

I have a script that will write information to cell A3 of sheet1 from a webpage , and another function of the script is to read the data that the is returned to sheet1 columns B-G . However if the data is the result of a formula it returns an undefined value. If the data isn't a formula it will return the value .. I hope i'm being clear with this .

So basically the formula : =VLOOKUP(A3,sheet2,2) is in cell B3 , I want to be able to transmit the data that is calculated in this cell to a webpage , however i don't know how to. I can get it to read normal text but not formulae

i don't really know what I'm doing :/
 
Old January 7th, 2005, 01:06 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Okay, I see. Can you show the code that you have so far, or a slimmed down version?



--

Joe (Microsoft MVP - XML)
 
Old January 10th, 2005, 09:45 AM
Registered User
 
Join Date: Jan 2005
Location: Hartlepool, Cleveland, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bRvO
Default

here's the code

<html>

<head>
    <title>Search Excel Document</title>

<script language="JavaScript">
<!--//






    function WriteExceldoc(){
    // creates the word object

    var doc1 = new ActiveXObject("Excel.Application");
    doc1.Visible=false;
    doc1.Workbooks.open("D:\\Documents and Settings\\WrighC01\\Desktop\\4 Secrets\\Email Users.xls");
    doc1.userControl=false;
    doc1.Range("A3").Select()
    doc1.ActiveCell="TeasdM01"

    // searches cell B3 for its contents and displays the value
    var txt1;
    var txt2;
    var txt3;
    var txt4;
    var txt5;
    var txt6;

    var cell1 = 'B3';
    var cell2 = 'C3';
    var cell3 = 'D3';
    var cell4 = 'E3';
    var cell5 = 'F3';
    var cell6 = 'G3';

    var findword1 = doc1.worksheets('sheet2').Range(cell1);
    var findword2 = doc1.worksheets('sheet2').Range(cell2);
    var findword3 = doc1.worksheets('sheet2').Range(cell3);
    var findword4 = doc1.worksheets('sheet2').Range(cell4);
    var findword5 = doc1.worksheets('sheet2').Range(cell5);
    var findword6 = doc1.worksheets('sheet2').Range(cell6);

    var isfound1 = doc1.worksheets('sheet2').Range(cell1).Find(findwo rd1);
    var isfound2 = doc1.worksheets('sheet2').Range(cell2).Find(findwo rd2);
    var isfound3 = doc1.worksheets('sheet2').Range(cell3).Find(findwo rd3);
    var isfound4 = doc1.worksheets('sheet2').Range(cell4).Find(findwo rd4);
    var isfound5 = doc1.worksheets('sheet2').Range(cell5).Find(findwo rd5);
    var isfound6 = doc1.worksheets('sheet2').Range(cell6).Find(findwo rd6);

    if(isfound1){txt1=findword1;}
    if(isfound2){txt2=findword2;}
    if(isfound3){txt3=findword3;}
    if(isfound4){txt4=findword4;}
    if(isfound5){txt5=findword5;}
    if(isfound6){txt6=findword6;}

    document.all.tbContentElement1.DOM.body.innerHTML = txt1;
    document.all.tbContentElement2.DOM.body.innerHTML = txt2;
    document.all.tbContentElement3.DOM.body.innerHTML = txt3;
    document.all.tbContentElement4.DOM.body.innerHTML = txt4;
    document.all.tbContentElement5.DOM.body.innerHTML = txt5;
    document.all.tbContentElement6.DOM.body.innerHTML = txt6;

    // quit Excel
    doc1.quit();
    }





//-->
</script>


</head>



<body>


<p><p><input type=button onClick="WriteExceldoc();" value="Load">
<p>


<p><input type=button onClick="loadExceldoc();" value="Load">
<p><input type=file name=hello>
<p>
<object ID="tbContentElement1" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="150" height="60">
<param name=Scrollbars value=false>
</object>

<object ID="tbContentElement2" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="325" height="60">
<param name=Scrollbars value=false>
</object>

<object ID="tbContentElement3" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="175" height="60">
<param name=Scrollbars value=false>
</object>

<object ID="tbContentElement4" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="100" height="60">
<param name=Scrollbars value=false>
</object>

<object ID="tbContentElement5" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="175" height="60">
<param name=Scrollbars value=false>
</object>

<object ID="tbContentElement6" CLASS="tbContentElement"
CLASSID="clsid:2D360201-FFF5-11D1-8D03-00A0C959BC0A" VIEWASTEXT
width="100" height="60">
<param name=Scrollbars value=false>
</object>


</body>

</html>

i don't really know what I'm doing :/
 
Old January 10th, 2005, 10:14 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

What is a tbContentElement?


--

Joe (Microsoft MVP - XML)
 
Old January 10th, 2005, 10:48 AM
Registered User
 
Join Date: Jan 2005
Location: Hartlepool, Cleveland, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bRvO
Default

its just a where the results will be displayed on the page . A text Area .

whatever is in say Cell B3 should be then be shown text area tbcontentelement1 .

as i've said it works when there's *normal* text in the cells , but returns an undefined value when a formula is there

i don't really know what I'm doing :/
 
Old January 10th, 2005, 11:23 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

But I wondered why you are using an object when you could just use a standard textbox or a textarea?

--

Joe (Microsoft MVP - XML)
 
Old January 10th, 2005, 11:58 AM
Registered User
 
Join Date: Jan 2005
Location: Hartlepool, Cleveland, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bRvO
Default

That's just how I'm used to doing it , sorry i'm a bit new to javascripting .

would using an object make a difference ?

i don't really know what I'm doing :/
 
Old January 10th, 2005, 12:23 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

What I meant was why are you using a dhtml edit control to display a value rather than a simple textbox?
Why not have a textbox, with id txtContent1, and change:
Code:
document.all.tbContentElement1.DOM.body.innerHTML = txt1;
to
Code:
txtContent1.value = txt1;

--

Joe (Microsoft MVP - XML)




Similar Threads
Thread Thread Starter Forum Replies Last Post
convert Excel formula to JavaScript amwith Javascript How-To 3 March 7th, 2008 06:31 AM
Excel to Javascript formula assistance recei Javascript How-To 1 February 6th, 2007 10:25 AM
Excel Formula zachtom Excel VBA 4 May 6th, 2006 08:02 PM
Formula in Excel davekrunal46 Excel VBA 1 December 5th, 2005 06:51 AM
converting formula in excel ct Excel VBA 0 October 5th, 2005 10:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.