JScript using Excel TextToColumns

Question

I've been trying to make this little snippet work but I've been failing miserably. The script seems to work alright, but the thing is: I need the third column (the one with the numbers) to be converted to text.

Looking at the help page in Excel it seems that the "Array(3, 2)" part would make it possible but even trying a lot of combinations of number and arguments on this function I couldn't make it work.

Any help with this 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));

Answer 1

There are two issues here.

The first issue is that the format you are using for the FieldInfo parameter is incorrect. The parameter should be an array-of-arrays. The JScript format for such an array would look like:

// 2 = xlTextFormat
[[1, 2], [2, 2], [3, 2]]
// or, if you prefer using the Array() function:
Array(Array(1, 2), Array(2, 2), Array(3, 2))

(In my testing, I had to specify all three columns. If you don't care about the other columns, set the second item of each array to 1 for xlGeneralFormat.)

However, it's not that simple, because Excel, like most COM objects, expects the array to be a SAFEARRAY structure. In VBScript, this isn't an issue because its arrays are implemented using SAFEARRAYs, but in JScript, the array is essentially a special type of object that is not easily converted to a SAFEARRAY (you can read more in this article from Eric Lippert's blog if you want more details). There is no built-in way to convert a JScript array to a SAFEARRAY, though there are workarounds (see this SO question, for example).

The second issue is that you are actually missing one parameter, OtherChar. Even though the documentation states that this parameter is only required when Other is True, you still have to supply some value since there are no optional parameters in JScript. In the example below, I expanded the function call and labelled each line using the parameter names from the documentation to make it 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");
    for (var i = 0; i < jsArr.length; i++)
    dict.add(i, jsArr[i]);
    return dict.Items();
}

Answer 2

The FieldInfo parameter needs to be an Array of Arrays, not the something you get back when you call the native JScript Array constructor without new.

So reduce complexity by modifying the column separately:

app.Range("A1").EntireColumn.TextToColumns(app.Range("B1"),1,1,false,false,false,true,false,false);
app.Range("D1").EntireColumn.NumberFormat = "@"