How to save user input in Excel from web page

onlyshails · #1 (**permalink**) May 11th, 2008, 05:44 AM

Hi,I have added a HTML code below. Please copy pate it in NOTEPAD and sait it with extention as " .HTML ".

**This web page is not uploaded . i AM GOING TO USE IT OFFLINE ***

open the the web page and pleae have a look at it and advise me once we fill up the form , how do we save the user input to a MS excel sheet.

***************************************

<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD>

<SCRIPT LANGUAGE=JAVASCRIPT>
var currentDate = new Date();
var date1 = currentDate.getDate();
var mon = currentDate.getMonth()+1;
var year = currentDate.getYear();
var today = date1+"/"+mon+"/"+year;
var filePath = "C:\Users\Izzy\Desktop\123456.xlsx";
function setDate()
{
f1.tDate.value=today;
}

function saveToExcel()
{
var myApp = new ActiveXObject("Excel.Application");
myApp.visible = true;
var xlCellTypeLastCell = 11;

var myWorkbook = myApp.Workbooks.Open(C:\Users\Izzy\Desktop\123456. xlsx);
var myWorksheet = myWorkbook.Worksheets(1);
myWorksheet.Activate;
objRange = myWorksheet.UsedRange;

objRange.SpecialCells(xlCellTypeLastCell).Activate ;
newRow = myApp.ActiveCell.Row + 1;
alert('newRow : '+newRow);
strNewCell = "A" + newRow;
alert('strNewCell : '+ strNewCell);
myApp.Range(strNewCell).Activate;
myWorksheet.Cells(newRow,1).value = f1.tDate.value;
myWorksheet.Cells(newRow,2).value = f1.tCaller.value;
myWorksheet.Cells(newRow,3).value = f1.tProduct.value;
myWorksheet.Cells(newRow,4).value = f1.tNatureOfProblem.value;
myWorksheet.Cells(newRow,5).value = f1.tSolutions.value;
myWorksheet.Cells(newRow,6).value = f1.tStatus.value;
myWorksheet.Cells(newRow,7).value = f1.tRemark.value;
myApp.Workbooks.Close;
myApp.Close;
alert('Data successfully saved');
}

</SCRIPT>
<BODY onLoad='setDate();'>
<form name="f1" method="post">
<h align="center"><b>Daily Report</b></h>

<table border="1" width="1000">
<tr>
<td width="250" height="25"></td>
<td width="200" class="tb_bor" align="center"><b>Date</b></td>
<td width="100" class="tb_bor" align="center"><b>Caller</b></td>
<td width="100" class="tb_bor" align="center"><b>Product</b></td>
<td width="150" class="tb_bor" align="center"><b>Nature Of Problem</b></b></td>
<td width="100" class="tb_bor" align="center"><b>Solutions</b></td>
<td width="100" class="tb_bor" align="center"><b>Status</b></td>
<td width="100" class="tb_bor" align="center"><b>Remark</b></td>
<td width="250"></td>
</tr>
<tr>
<td width="250" height="25"></td>
<td width="200" class="tb_bor" align="center"><input type=text name=tDate ></td>
<td width="100" class="tb_bor" align="center"><input type=text name="tCaller" ></td>
<td width="100" class="tb_bor" align="center"><input type=text name="tProduct" ></td>
<td width="150" class="tb_bor" align="center"><input type=text name="tNOP" ></td>
<td width="100" class="tb_bor" align="center"><input type=text name="tSolutions" ></td>
<td width="100" class="tb_bor" align="center"><input type=text name="tStatus" ></td>
<td width="100" class="tb_bor" align="center"><input type=text name="tRemark" ></td>
<td width="250"></td>
</tr>
</table>
<br>
<table align="center">
<input type="button" name="next" value="Next">
<input type="reset" name="clear" value="Clear">
<input type="button" name=save value="Save" onClick='saveToExcel();'>
</table>
</form>
</BODY>
</HTML>
***************************************

joefawcett · #2 (**permalink**) May 11th, 2008, 05:58 AM

I can see three problems

Your path:

Code:

var filePath = "C:\Users\Izzy\Desktop\123456.xlsx";

should be

Code:

var filePath = "C:\\Users\\Izzy\\Desktop\\123456.xlsx";

and then you don't use the variable later:

Code:

var myWorkbook = myApp.Workbooks.Open(C:\Users\Izzy\Desktop\123456.xlsx);

You don't specify to save changes:
Code:
```
myWorkbook.Close(true);
```
Security permissions must be altered to allow saving to a local folder. If you are running this file directly from your machine consider renaming it to .HTA and look at HTML Applications on msdn.com.

--

Joe (Microsoft MVP - XML)

onlyshails · #3 (**permalink**) May 11th, 2008, 06:13 AM

HI, Thanks for the INSTANT REPLY ..i really appreciate that ..i am not too good with java script ...but good with html ..is there any example that u could post or a link that i can visit that explains how it is done as i am working on this since morning ..and there's NO luck and have to make sure this is done by next day morning ..thanks for the help.

joefawcett · #4 (**permalink**) May 11th, 2008, 07:25 AM

Firstly, what did you change, what suggestions did you try?
Try searching this forum, for example: http://p2p.wrox.com/topic.asp?TOPIC_ID=28026

--

Joe (Microsoft MVP - XML)

onlyshails · #5 (**permalink**) May 11th, 2008, 07:42 AM

The excel file that i have saved on my machine does not open nor any user input from the web page is tranferred to it.

If u have any altogether different code or a way please advise on it .

About ur suggestions ....

Your path:
var filePath = "C:\Users\Izzy\Desktop\123456.xlsx";

should be
var filePath = "C:\\Users\\Izzy\\Desktop\\123456.xlsx";

*** I have made the above change ***

and then you don't use the variable later:
var myWorkbook = myApp.Workbooks.Open(C:\Users\Izzy\Desktop\123456. xlsx);

***as i informed i am not god with javascript , i am unable to understand the above suggestion ****

You don't specify to save changes:
myWorkbook.Close(true);

***as i informed i am not god with javascript , i am unable to understand the above suggestion ****

joefawcett · #6 (**permalink**) May 11th, 2008, 07:54 AM

Okay, but the forum is for people trying to learn, not to do the work for someone because they're under time pressure.

Change:

Code:

var myWorkbook = myApp.Workbooks.Open(C:\Users\Izzy\Desktop\123456.xlsx);

to:

Code:

var myWorkbook = myApp.Workbooks.Open(filePath);

Add:

Code:

myWorkbook.Close(true);

here:

Code:

myWorksheet.Cells(newRow,7).value = f1.tRemark.value;
myWorkbook.Close(true);
myApp.Workbooks.Close;
myApp.Close;

--

Joe (Microsoft MVP - XML)

Need to download code?

Navigation