Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Excel Web App Mashups > Web controls
Deze pagina in het Nederlands

Creating webpages with Excel Web App mashups

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)&nbsp;
    <input name="Phase" size="18" style="width: 112px" />
    Frequency ratio&nbsp;
    <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) (12/22/2011 4:04:33 PM)

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 (12/23/2011 6:37:52 AM)

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 (12/29/2011 4:38:20 AM)

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 (12/29/2011 7:07:19 AM)

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 (1/2/2013 10:24:10 PM)

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 (1/5/2013 8:32:04 PM)

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 (1/19/2014 8:54:53 AM)

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 (1/19/2014 12:42:14 PM)

Hi Abraham,

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

 


Comment by: Puneet (4/15/2015 1:17:05 PM)

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 (4/15/2015 7:59:33 PM)

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 (10/6/2016 6:32:20 AM)

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 (10/11/2016 7:07:14 AM)

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 (10/26/2017 1:15:43 AM)

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 Im no programmer.....

Cheers jim

 


Comment by: Jan Karel Pieterse (10/26/2017 9:15:50 AM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].