JScript using Excel TextToColumns -

i've been trying make little snippet work i've been failing miserably. script seems work alright, thing is: need third column (the 1 numbers) converted text.

looking @ page in excel seems "array(3, 2)" part make possible trying lot of combinations of number , arguments on function couldn't make work.

any problem?

var app = new activexobject("excel.application");  app.visible = true;  var xls = app.workbooks.add();    app.cells(1, 1).value = "olympia, wa, 123";  app.cells(2, 1).value = "salem, or, 3434";  app.cells(3, 1).value = "boise, id, 342";  app.cells(4, 1).value = "sacramento, ca, 3";    app.range("a1").entirecolumn.texttocolumns(app.range("b1"),1,1,false,false,false,true,false,false,array(3, 2));

there 2 issues here.

the first issue format using fieldinfo parameter incorrect. parameter should array-of-arrays. jscript format such array like:

// 2 = xltextformat [[1, 2], [2, 2], [3, 2]] // or, if prefer using array() function: array(array(1, 2), array(2, 2), array(3, 2)) 

(in testing, had specify 3 columns. if don't care other columns, set second item of each array 1 xlgeneralformat.)

however, it's not simple, because excel, com objects, expects array safearray structure. in vbscript, isn't issue because arrays implemented using safearrays, in jscript, array special type of object not converted safearray (you can read more in this article eric lippert's blog if want more details). there no built-in way convert jscript array safearray, though there workarounds (see this question, example).

the second issue missing 1 parameter, otherchar. though documentation states parameter required when other true, still have supply value since there no optional parameters in jscript. in example below, expanded function call , labelled each line using parameter names documentation make clear:

var app = new activexobject("excel.application"); app.visible = true; var xls = app.workbooks.add();  app.cells(1, 1).value = "olympia, wa, 123"; app.cells(2, 1).value = "salem, or, 3434"; app.cells(3, 1).value = "boise, id, 342"; app.cells(4, 1).value = "sacramento, ca, 3";  app.range("a1").entirecolumn.texttocolumns(     app.range("b1"),  // destination     1,                // datatype     1,                // textqualifier     false,            // consecutivedelimiter     false,            // tab     false,            // semicolon     true,             // comma     false,            // space     false,            // other     '',               // otherchar     getsafearray([         getsafearray([1, 2]),         getsafearray([2, 2]),         getsafearray([3, 2])     ])                // fieldinfo );  // ref: https://stackoverflow.com/a/5910730 function getsafearray(jsarr) {     var dict = new activexobject("scripting.dictionary");     (var = 0; < jsarr.length; i++)     dict.add(i, jsarr[i]);     return dict.items(); }