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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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

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

 


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)

 


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].