0

I recently started new project at work where I would like to work a bit with JavaScript. It's my first contact with JS and I am already struggling with one reference.

I am trying to open an workbook (Excel) using JS, but it's secured by password. I don't know what is proper syntax to pass optional objects such as "Password:=***". Here is what I have:

function test() {
    var Excel = new ActiveXObject("Excel.Application");
    var Location = "W:/MyFolder/MyFile.xlsm"
    Excel.Visible = true;
    var obj = Excel.Workbooks.Open(Location:="****");
}

It works fine, it opens the file as intended however I can't add password variable - for example:

var obj = Excel.Workbooks.Open(Location, Password:="****");

Any ideas?

Community
  • 1
  • 1
Doctore
  • 11
  • 1
  • 4
  • Possible duplicate of [Named parameters in javascript](http://stackoverflow.com/questions/11796093/named-parameters-in-javascript) – GSerg Sep 17 '16 at 12:36

2 Answers2

1

The problem is that there seems not to be a method to omit optional arguments with JScript and ActiveX-functions. Simply omit them like in :

var obj = Excel.Workbooks.Open(Location, , , , "password");

is a syntactical error in JScript.

Using undefined seems to lead to 0 in ActiveX-functions. So 

var obj = Excel.Workbooks.Open(Location, undefined, undefined, undefined, "password");

will not work because the fourth argument Format cannot be 0. It must be 1 to 6 according to Workbooks.Open Method (Excel)

So the only way seems to be not to omit but to hand over possible values for them:

var obj = Excel.Workbooks.Open(Location, 0, false, 1, "password");

will work.

Edit:

In this special case, since all optional arguments are System.Objects, to hand over null for optional arguments works for me:

var obj = Excel.Workbooks.Open(Location, null, null, null, "password");
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
0

Checkout the Workbook.Open MSDN page. Basically, the password is expected as the 5th argument. 

var obj = Excel.Workbooks.Open(Location, missing, missing, missing, "****");

You can specify the 2nd-4th arguments as missing/null. Not sure how to do this in javascript; in PowerShell it's $missing = [System.Reflection.Missing]::Value and in c# it's Type.Missing

Community
  • 1
  • 1
G42
  • 9,791
  • 2
  • 19
  • 34
  • Thank you for your answer. I am familliar with msdn .open, the problem is in JS syntax - I cannot apply it in JS :) The "missing" parameter makes sense, although it doesnt work (neither "null").... – Doctore Sep 17 '16 at 15:25