Save and quit ActiveX Object

Question

I am trying to use javascript to send data to a excel-list, add the data to the list, save and close the list. I know when using ActiveX I am limited to IE but thats ok.

The problem I am facing is that neither the quit nor the save method work. The process stays open and the data is not saved, unless I manually save it.

Heres the code:

function getNumber() {

    var Excel = new ActiveXObject("Excel.Application");

    var test = Excel.Workbooks.Open("pathToFile");

    var LastRow = test.ActiveSheet.Range("A1").CurrentRegion.Rows.Count;

    alert(LastRow);
    var combine = "A" + (LastRow);

    alert(combine);

    var getCell = test.ActiveSheet.Range(combine).Value;

    var delimiter = '_';
    var start = 3;
    var tokens = getCell.split(delimiter).slice(start);
    var result = parseInt(tokens.join(delimiter));
    var newNumber = result + 1; 

    whichCompany();

    var newRow = "A" + (LastRow + 1);

    var id = "D_" + selectedName + "_2017_"+ newNumber.toString();
    test.ActiveSheet.Range(newRow).Value = id;
    alert(id);

    newRow = "B" + (LastRow + 1);

    var m_names = new Array("Jan", "Feb", "Mar", 
    "Apr", "May", "Jun", "Jul", "Aug", "Sep", 
    "Oct", "Nov", "Dec");

    var d = new Date();
    var curr_date = d.getDate();
    var curr_month = d.getMonth();
    var curr_year = d.getFullYear();
    var dateNow = curr_date + m_names[curr_month] + curr_year;

    test.ActiveSheet.Range(newRow).value = dateNow;

    newRow = "C" + (LastRow + 1);
    test.ActiveSheet.Range(newRow).Value = document.getElementById("fName").value + " " + document.getElementById("lName").value;

    newRow = "D" + (LastRow + 1);
    internalOrExternal();
    test.ActiveSheet.Range(newRow).Value = intOrExt;

    newRow = "E" + (LastRow + 1);
    test.ActiveSheet.Range(newRow).Value = document.getElementById("case").value;

    newRow = "F" + (LastRow + 1);
    test.ActiveSheet.Range(newRow).Value = document.getElementById("produkt").value;

    Excel.Quit();

}

Answer 1

Ok so for anyone interested in the solution. The mistake I made was I didnt fill out the form completely so the code skips the parts from this line of code on: newRow = "B" + (LastRow + 1);

This will cause the quit() method to never be called. When commenting out all the code which I didnt really use when testing, the code works without any problem.

As additional info if anyone ever faces a similiar problem, when using the saveAs()-method, you can save the file to the same filename so saveas() can function as a simple save.

Please see the code below which worked for me ( the code I didnt use for the test case is commented out. This is the code which was skipped before, together with the two methods save() and quit()):

function getNumber() {

    var Excel = new ActiveXObject("Excel.Application");

    var test = Excel.Workbooks.Open("pathToFile");

    var LastRow = test.ActiveSheet.Range("A1").CurrentRegion.Rows.Count;

    alert(LastRow);
    var combine = "A" + (LastRow);

    alert(combine);


    var getCell = test.ActiveSheet.Range(combine).Value;


    var delimiter = '_';
    var start = 3;
    var tokens = getCell.split(delimiter).slice(start);
    var result = parseInt(tokens.join(delimiter));
    var newNumber = result + 1; 

    whichCompany();

    var newRow = "A" + (LastRow + 1);

    var id = "D_" + selectedName + "_2017_"+ newNumber.toString();
    test.ActiveSheet.Range(newRow).Value = id;
    alert(id);

    <!-- newRow = "B" + (LastRow + 1); -->

    <!-- var m_names = new Array("Jan", "Feb", "Mar",  -->
    <!-- "Apr", "May", "Jun", "Jul", "Aug", "Sep",  -->
    <!-- "Oct", "Nov", "Dec"); -->

    <!-- var d = new Date(); -->
    <!-- var curr_date = d.getDate(); -->
    <!-- var curr_month = d.getMonth(); -->
    <!-- var curr_year = d.getFullYear(); -->
    <!-- var dateNow = curr_date + m_names[curr_month] + curr_year; -->

    <!-- test.ActiveSheet.Range(newRow).value = dateNow; -->

    <!-- newRow = "C" + (LastRow + 1); -->
    <!-- test.ActiveSheet.Range(newRow).Value = document.getElementById("fName").value + " " + document.getElementById("lName").value; -->

    <!-- newRow = "D" + (LastRow + 1); -->
    <!-- internalOrExternal(); -->
    <!-- test.ActiveSheet.Range(newRow).Value = intOrExt; -->

    <!-- newRow = "E" + (LastRow + 1); -->
    <!-- test.ActiveSheet.Range(newRow).Value = document.getElementById("case").value; -->

    <!-- newRow = "F" + (LastRow + 1); -->
    <!-- test.ActiveSheet.Range(newRow).Value = document.getElementById("produkt").value; -->

    test.SaveAs("pathToFile");
    Excel.Application.Quit();
}

@Shilly: Thanks for the link to the documentation, it helped to get me on the way to the solution.