We help IT Professionals succeed at work.

vbScript to JavaScript

tony_813
tony_813 asked
on
Medium Priority
669 Views
Last Modified: 2012-08-13
Hello Everyone,
  How can i recreate the following to JavaScript:

    <script language="vbscript" type="text/vbscript">
        sub cmdEnter_onclick()
            dim dValue
            dim objExcel
            dim objPhonebook
            dim dWindowState
            dWindowState = -4143
            set objExcel = createobject("Excel.Application")
            objExcel.Workbooks.Open "C:\dPath\D-Assistant.xls", False, , , "password"
            lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row
            dValue = document.form1.txtEntry.value & "*"
            objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5:A" & lstRow).AutoFilter 1, dValue, , , False
            document.form1.txtEntry.value = ""
            objExcel.WindowState = dWindowState
            objExcel.Height = 400
            objExcel.Width = 768
            objExcel.Top = 318
            objExcel.Left = 2.5
            objExcel.visible = true
            objExcel.ActivateMicrosoftApp
        end sub
    </script>

thnx in advance
tony_813
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Because of the Excel stuff it's hard to know for sure if this will work without trying it (odds are it won't the first time)


    <script language="javascript">
      function cmdEnter_onclick()
      {
            // var objPhonebook;  //  not used
            var dWindowState = -4143;
            var objExcel = createobject("Excel.Application");

            objExcel.Workbooks.Open( "C:\dPath\D-Assistant.xls", false, null, null, "password" );
            lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row
            var dValue = document.form1.txtEntry.value + "*";
            objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5:A" & lstRow).AutoFilter( 1, dValue, null, null, false );
            document.form1.txtEntry.value = "";
            objExcel.WindowState = dWindowState;
            objExcel.Height = 400;
            objExcel.Width = 768;
            objExcel.Top = 318;
            objExcel.Left = 2.5;
            objExcel.visible = true;
            objExcel.ActivateMicrosoftApp();

      }

    </script>
CERTIFIED EXPERT

Commented:
... this is because I don't know what the exact property and method names are on the Excel.Application object.  For example, it may be necessary to lower-case many of the properties for javascript:

            objExcel.height = 400;
            objExcel.width = 768;
            objExcel.top = 318;
            objExcel.left = 2.5;
            objExcel.visible = true;
CERTIFIED EXPERT

Commented:
... and here's one I missed that I should have known:

            var objExcel = CreateObject("Excel.Application");

CERTIFIED EXPERT

Commented:
oops!  I meant:

   var objExcel = new ActiveXObject("Excel.Application");
CERTIFIED EXPERT

Commented:
and I don't know about this one either ... if it is a method it needs parens at the end:

            lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row()
CERTIFIED EXPERT
Commented:
putting it all together now ... give this a try:


    <script language="javascript">
      function cmdEnter_onclick()
      {
            var dWindowState = -4143;
            var objExcel = new ActiveXObject("Excel.Application");
// note the double-slashes below:
            objExcel.Workbooks.Open( "C:\\dPath\\D-Assistant.xls", false, null, null, "password" );
            lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row();
            var dValue = document.form1.txtEntry.value + "*";
            objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5:A" & lstRow).AutoFilter( 1, dValue, null, null, false );
            document.form1.txtEntry.value = "";
            objExcel.WindowState = dWindowState;
            objExcel.height = 400;
            objExcel.width = 768;
            objExcel.top = 318;
            objExcel.left = 2.5;
            objExcel.visible = true;
            objExcel.ActivateMicrosoftApp();
      }
</script>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hello,
   thank you for your quick response...
   your in the right path....
   excel is starting up.... but is not opening the workbook...
   Let me watch Sopranos.. and I'll be back to play with it...

thnx
tony_813
CERTIFIED EXPERT

Commented:
note that you must double-slash the path to your workbook (see above)

Author

Commented:
Hello....
   I have the following... but i'm still not able to get the workbook to open..

      function cmdEnter_onclick()
      {
            var dWindowState = -4143;
            var objExcel = new ActiveXObject("Excel.Application");

            objExcel.Workbooks.Open("C:\\Documents and Settings\\Tony\\Desktop\\HTML\\D-Assistant.xls", false, null, null, "password");
            var lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row();
            var dValue = document.form1.txtEntry.value + "*";
            objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5:A" + lstRow).AutoFilter( 1, dValue, null, null, false );
            document.form1.txtEntry.value = "";
            objExcel.WindowState = dWindowState;
            objExcel.Height = 400;
            objExcel.Width = 768;
            objExcel.Top = 318;
            objExcel.Left = 2.5;
            objExcel.Visible = true;
            objExcel.ActivateMicrosoftApp();

      }

hmmmmmm....
I checked the password to make sure is right.. and it is...
any other ideas on what could be wrong?

Author

Commented:
ok ok... I made the following adjustment... and it turns out that the code will open the workbook..
but there must be something else wrong...
let me play with it some more...

Author

Commented:
ok got it to work...
it had to do with the following line... on where it was located...
apperently for JavaScript..... we have to make the application visible before anything can work...
other minor adjustments.... however, works great.. and its what I was looking for....
thnx
      function cmdEnter_onclick()
      {
            var dWindowState = -4143;
            var objExcel = new ActiveXObject("Excel.Application");
            objExcel.Visible = true;
            objExcel.Workbooks.Open("C:\\Documents and Settings\\Tony\\Desktop\\HTML\\D-Assistant.xls", false, null, null, "password");
            var lstRow = objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5000").End(3).Row;
            alert(lstRow)
            var dValue = document.form1.txtEntry.value + "*";
            objExcel.Workbooks("D-Assistant").Worksheets("List").Range("A5:A" + lstRow).AutoFilter( 1, dValue, null, null, false );
            document.form1.txtEntry.value = "";
            objExcel.WindowState = dWindowState;
            objExcel.Height = 400;
            objExcel.Width = 768;
            objExcel.Top = 318;
            objExcel.Left = 2.5;
            objExcel.ActivateMicrosoftApp();

      }
CERTIFIED EXPERT

Commented:
great!  the only thing I'm unsure about is the last line ...

   objExcel.ActivateMicrosoftApp();

apparently it takes a numeric parameter -- but I can't find anything on what it means.  But if it works for you as is then maybe we shouldn't fix what isn't broke.  :)

Author

Commented:
ohhh
that last line i meant to remove...
from the Excel Application you can use the ActivateMicrosoftApp method to activate one of the following
MS Applications:

xlMicrosoftWord
xlMicrosoftPowerPoint
xlMicrosoftMail
xlMicrosoftAccess
xlMicrosoftFoxPro
xlMicrosoftProject
xlMicrosoftSchedulePlus

If the application is already running, this method activates the running application. If the application isn't running, this method starts a new instance of the application.

The same as using:

GetObject or CreateObject

tony_813
CERTIFIED EXPERT

Commented:
I see now ... the list may actually be longer than that.  I was playing around with it and discovered that passing in a 1 opens FoxPro (or attempts to) and passing in a -1 opens Solitare!  I didn't feel like traversing thru all possible integers, but maybe the next time I'm really really bored ...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.