I have a HTML web form with 5 text inputs and a button calling a JavaScript function. The function as it stands, will open an existing Excel file, find the last populated row, insert a new row below it and put the form values in, one value to each cell. It then saves and exits.

The spreadsheet however has many more than 5 columns, so the function only ever places the data into columns A - E.

function createData(){

    var first = document.getElementById('A').value;
    var second = document.getElementById('B').value;
    var third = document.getElementById('C').value;
    var fourth = document.getElementById('D').value;
    var fifth = document.getElementById('E').value;

    var xlDown = -4121

    var w =new ActiveXObject("Excel.Application");
    w.Visible=true;

    w.Workbooks.Open("file:\\Form.xls");
    objWorksheet = w.Worksheets(1);

    objRange = w.Range("A1");
    objRange.End(xlDown).Activate;
    intNewRow = w.ActiveCell.Row + 1;

    for (i=1; i<10000; i++){
        objWorksheet.Cells(intNewRow, 1) = first;
        objWorksheet.Cells(intNewRow, 2) = second;
        objWorksheet.Cells(intNewRow, 3) = third;
        objWorksheet.Cells(intNewRow, 4) = fourth;
        objWorksheet.Cells(intNewRow, 5) = fifth;
    }
    w.ActiveWorkbook.SaveAs("file:\\Form.xls");
    w.Quit();
              alert("The data has been added to the spreadsheet");
}

What I actually want to do is this:

The spreadsheet may or may not already have data in columns A - G on each row. I want the function to search column B of the existing sheet for the text value of "first" (as defined above). If found, insert the other 4 values in corresponding cells in columns H-K. If not found then insert all 5 values into a new row "first" in Column B and the rest in column H-K.

I've been messing with this for a long time and can't seem to crack it, though I know it can be done.

Any help would be greatly appreciated!

I have a HTML web form with 5 text inputs and a button calling a JavaScript function. The function as it stands, will open an existing Excel file, find the last populated row, insert a new row below it and put the form values in, one value to each cell. It then saves and exits.

The spreadsheet however has many more than 5 columns, so the function only ever places the data into columns A - E.

function createData(){

    var first = document.getElementById('A').value;
    var second = document.getElementById('B').value;
    var third = document.getElementById('C').value;
    var fourth = document.getElementById('D').value;
    var fifth = document.getElementById('E').value;

    var xlDown = -4121

    var w =new ActiveXObject("Excel.Application");
    w.Visible=true;

    w.Workbooks.Open("file:\\Form.xls");
    objWorksheet = w.Worksheets(1);

    objRange = w.Range("A1");
    objRange.End(xlDown).Activate;
    intNewRow = w.ActiveCell.Row + 1;

    for (i=1; i<10000; i++){
        objWorksheet.Cells(intNewRow, 1) = first;
        objWorksheet.Cells(intNewRow, 2) = second;
        objWorksheet.Cells(intNewRow, 3) = third;
        objWorksheet.Cells(intNewRow, 4) = fourth;
        objWorksheet.Cells(intNewRow, 5) = fifth;
    }
    w.ActiveWorkbook.SaveAs("file:\\Form.xls");
    w.Quit();
              alert("The data has been added to the spreadsheet");
}

What I actually want to do is this:

The spreadsheet may or may not already have data in columns A - G on each row. I want the function to search column B of the existing sheet for the text value of "first" (as defined above). If found, insert the other 4 values in corresponding cells in columns H-K. If not found then insert all 5 values into a new row "first" in Column B and the rest in column H-K.

I've been messing with this for a long time and can't seem to crack it, though I know it can be done.

Any help would be greatly appreciated!

• javascript
• excel
• activexobject

Share Improve this question Follow edited Nov 24, 2011 at 1:21 Yi Jiang 50.2k1616 gold badges139139 silver badges136136 bronze badges asked Nov 23, 2011 at 23:05 LongmanLongman 14311 silver badge99 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

Well it seems I found an answer by trial and error. She aint pretty, but it works. I'll look at optimizing the code as time allows.

function createData(){      

var Name = document.getElementById('txtName').value;

var first = document.getElementById('A').value;     
var second = document.getElementById('B').value;     
var third = document.getElementById('C').value;     
var fourth = document.getElementById('D').value;     
var fifth = document.getElementById('E').value;      
var xlDown = -4121      

var w =new ActiveXObject("Excel.Application");     
w.Visible=true;      
w.Workbooks.Open("file:\\Form.xls");     

objWorksheet = w.Worksheets(1);      
objRange = w.Range("H3");     
objRange.End(xlDown).Activate;     
intNewRow = w.ActiveCell.Row + 1;      

for (i=4; i<10; i++){
    var a = objWorksheet.Cells(i,2);
    var b = a.Value;
    if(b == Name){
        a.Activate;
        b = w.ActiveCell.Offset(0,6);
        intNew = w.ActiveCell.Row;
        objWorksheet.Cells(intNew, 8) = first
        objWorksheet.Cells(intNew, 9) = second
        objWorksheet.Cells(intNew, 10) = third
        objWorksheet.Cells(intNew, 11) = fourth
        objWorksheet.Cells(intNew, 12) = fifth
    }   
    else{
        objWorksheet.Cells(intNewRow, 8) = first
        objWorksheet.Cells(intNewRow, 9) = second
        objWorksheet.Cells(intNewRow, 10) = third
        objWorksheet.Cells(intNewRow, 11) = fourth
        objWorksheet.Cells(intNewRow, 12) = fifth
    }
}
w.ActiveWorkbook.SaveAs("file:\\\\Test.xls");
w.Quit();
}