Creating webpages with Excel Web App mashups

Pages in this article

Getting input from web form controls into your Excel file

Wouldn't it be nice to have a web form which users can enter data into and have your Excel Web App updated with the content of the web form. That is what I describe here.

Put up a form with some controls

To get data from a form textbox into your Excel file, you need to place a form on your web page. This piece of html does that:

<form name="ChangeInput">
    Phase difference (degrees) 
    <input name="Phase" size="18" style="width: 112px" />
    Frequency ratio 
    <input name="Freq" size="18" style="width: 112px" />
    <input onclick="JavaScript:SetValuesInSheet();" type="button" value="Update Chart" />
</form>

As you can see, the button's click event is set to call a Javascript function called SetValuesInSheet.

Writing values in a cell

The SetValuesInSheet function is relatively short:

function SetValuesInSheet()
    {
    // Get a reference to where we need to write
    ewaCht.getActiveWorkbook().getRangeA1Async('b1:b2', getRangeA1Callback, null);
    }

As you can see, we first need to ask the Ewa to give us a reference to range B1:B2 in the active sheet. This is done asynchronously, so a call-back function is needed: getRangeA1Callback. Listed below:

function getRangeA1Callback(asyncResult)
        {
        // getRangeA1Async returns a range object through the AsyncResult object.
        var range = asyncResult.getReturnValue();
        var values = new Array(2);
        values[0] = new Array(1);
        values[1] = new Array(1);
        var control = document.ChangeInput.Phase;
        values[0][0] = control.value;
        control = document.ChangeInput.Freq;
        values[1][0] = control.value;
        // Now send the array to the range.
        range.setValuesAsync(values,Dummy,null);
}

If you want to push data into cells, you always need to use an array, like the code above shows.

Because the setValuesAsync is -again- asynchronous, it requires a callback, for which I included an empty function called Dummy (not shown here). NB: Dummy can be replaced with "null", but I left it in because the code works as it is.

So does this all work? Have a look at the next page!

Comments

Showing last 8 comments of 14 in total (Show All Comments):

Comment by: Abraham Pak (19-1-2014 08:54:53) deeplink to this comment

Hi,
I have an Excel file with multiple sheets and some of the cells have drop down menus. The drop down menus are shown very well when I embed the <iframe> code, but for some future programming reasons I have to use the javascript. Unfortunately when I embed the <div> code -no matter with which tags- the drop down menu (including its arrow head and drop down list) is shown one cell ahead of the containing cell itself.
Please let me know whether there's a workaround for this.
Thanks in advance.

Comment by: Jan Karel Pieterse (19-1-2014 12:42:14) deeplink to this comment

Hi Abraham,

DO you have a url I can go to to view what you mean?

Comment by: Puneet (15-4-2015 13:17:05) deeplink to this comment

Can a Excel VBA User Form , be displayed on a webpage.

The objective is - that users from varied locations can open the webpage, and automatically the VBA userform is displayed to them, into which they can enter data, which can be stored into excel worksheet.

Comment by: Jan Karel Pieterse (15-4-2015 19:59:33) deeplink to this comment

Hi Puneet,

No that isn't possible. However, you can create a Survey from OneDrive.com, which is free if you have a Microsoft account.

Comment by: Suraj Desai (6-10-2016 06:32:20) deeplink to this comment

Hello Sir,

I want to modify Excel Sheet on webpage and save changes to back. Can we do that? and how?

Comment by: Jan Karel Pieterse (11-10-2016 07:07:14) deeplink to this comment

Hi Suraj,

I only know of two ways to edit Excel files on-line:

- From OneDrive Sharepoint or Dropbox
- By creating a survey (which can be embedded in any webpage)

Comment by: James Norton (26-10-2017 01:15:43) deeplink to this comment

Hi I would really appreciate some help. I have created a quiz in excel And have this set up as a webapp. It includes a leaderboard with name In column j and a score in column k.this leaderboard updates when Someone plays the quiz.so far so good. However the scores do not persist When the app is closed. I need a way of detecting changes to the leaderboard
Stored in the workbook and then writing the updated leaderboard back To the excel workbook. Can you help? The cells that need updating Are j154 to k163.

Something like what you did above could be the answer but I’m no programmer.....

Cheers jim

Comment by: Jan Karel Pieterse (26-10-2017 09:15:50) deeplink to this comment

Hi Jim,

I guess what you need is what is called a survey. If you go to your OneDrive, you can create a new Survey. The results of the survay are written to an Excel workbook.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.