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

All comments about this page:

Comment by: Jim Maiolo (MSTF) (22-12-2011 16:04:33) deeplink to this comment

As another alternative to using getRangeA1Async, you can write code like this:

var range = ewaCht.getActiveWorkbook().getActiveSheet().getRange(1, 0, 1, 1);
...
range.setValuesAsync(values, null, null);

This eliminates one of the asynchronous steps by using index-based range selection (which is 0-based):
"B1:B2" -> first row = 1, first column = 0, last row = 1, last column = 1

Also note that it is valid to pass in null for the callback.

Comment by: Jan Karel Pieterse (23-12-2011 06:37:52) deeplink to this comment

Hi Jim,

Nice to see someone from the MSFT team respond here!

Thanks for the tip. I did see the synchronous getRange method later on when I browsed the MSDN content on the Ewa object. I decided to leave it as is in the article.

Comment by: James Dansey (29-12-2011 04:38:20) deeplink to this comment

Hi Jan/Jim,

I am struggling to troubleshoot a little, perhaps you could help me?

If I wish to pass values to a cell named "SiteControl" in a sheet named "Brain", would it be correct to define the reference as follows?:

ewaCht.getActiveWorkbook().getRangeA1Async('Brain!SiteControl', getRangeA1Callback, null);

Everything is working perfectly so far, I just seem unable to get values to write!

Great work though. Very impressive tutorial!

James.

Comment by: Jan Karel Pieterse (29-12-2011 07:07:19) deeplink to this comment

Hi James,

Have a look at this page which shows how to get a specific worksheet:

http://msdn.microsoft.com/en-us/library/ee660110.aspx

Comment by: Barry Johnson (2-1-2013 22:24:10) deeplink to this comment

Do you have an even simpler example? Sorry I'm new to this. I want to know what code to use to take a yes/no value from a drop down button and push that change into a single cell on on the sheet.

I can't figure out from your code what to delete to make this happen. Soeey

Comment by: Jan Karel Pieterse (5-1-2013 20:32:04) deeplink to this comment

Hi Barry,

Basically, the code up here should do the trick, because the only difference is that you need one cell to be changed and this example has two.

However, you could add a pivot table to your workbook which only has one field, which contains Yes and No. Then insert a slicer to the workbook using that field. You can place that slicer anywhere in your workbook and then use the filtered pivot table to extract the slicer filter. That way you don't need this JavaScript altogether.

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.