Warning: this is a long post. Get supplies so you don’t dehydrate or suffer from low blood sugar levels before reaching the end 🙂 Don't let the length of this post put you off though. The process is farily straight forward and there aren't really that many steps...honest.
Embedded below is an interactive Excel dashboard using Excel Web App, which is hosted in the cloud on OneDrive (previously SkyDrive). It includes password security so that each salesperson can only view their own sales.
Go ahead and test it by selecting a different username from the list beside 'Choose User'. The passwords are:
Bob: bpw
John: jpw
Richard: rpw
Press ENTER after typing in the password to update the report.
Before we get too excited I should make something clear. This information isn’t 100% secure, but then neither is a password protected Excel workbook.
Tip: You should know that a password protected Excel workbook can be hacked into with a little VBA knowledge, or the desire to search Google until you find an answer.
This technique (which I'm about to share with you) is protected from the majority of users, however anyone who knows JavaScript, or even a very determined user will eventually do enough Google searches and find a way to download the actual workbook. Once they have that then it’s a matter of figuring out how to unhide the sheets, and Bob’s your Uncle, they’ll have access to the underlying data for all salespeople.
That said, I’m going to show you how to make it very difficult for them, and it’s probably more secure than a password protected Excel workbook you distribute via email or give users access to via a network.
I say ‘it’s probably more secure than a password protected Excel workbook’ because this whole Excel Web App thing is quite new so there won’t be a lot of documentation on how to crack this code….. for now anyway. Plus most users wouldn’t even consider trying to figure it out.
Now, because this post is so long I've done up a TOC with hyperlinks so you can navigate through it quickly if you want to skip ahead. There's no extra charge for that convenience either 😉
Table of Contents
When to use Excel Web App for Report Distribution
Benefits of Excel Web App
Things You'll Need
Get Your Excel Dashboard Ready
Upload it to OneDrive
Create Your Web Page
Hide Excel Web App Viewer Bar - Optional
Workbook Layout Problems in WordPress
Caveat
More OneDrive Uses
When to use Excel Web App for Report Distribution
- This is the perfect alternative to distributing your password protected Excel reports via email.
- Although it’s not 100% secure, it's still good for information you’d rather people not see but, if it were to get out then it’s not going to cause World War III. And if you keep it on your Intranet then only employees have access.
- The drop down list means it’s suitable if you want to distribute reports and tailor what each individual sees without the need to create a separate workbook for each person.
- It's also a great alternative for Excel phobic users who like you to send their reports as a PDF or PowerPoint presentation, or worse, print them out since the interface is fairly basic, yet it still maintains the functionality of Excel and the interactive elements.
Benefits of Excel Web App
- Any changes you make to the workbook will be reflected automatically in the embedded Excel Web App view. If you update your reports with the latest data regularly then you don’t need to email the workbook out or even send a new link. The web page will reflect the latest version of the workbook when you refresh the page. Happy days!
- The report can be viewed on any web browser. No need for Excel to be installed.
- It maintains its interactivity just like it was in Excel. Note: Excel Form Controls (the ones you insert via the Developer tab in Excel) don't work in the Web App... yet. So interactivity must be achieved with data validation lists or Slicers. Alternatively if you know JavaScript then you can use it to control HTML Form Controls but that's a lesson for another day.
- Data typed into the Excel Web App, like the password, doesn't get written back to the Excel file on OneDrive, thus maintaining data integrity and password confidentiality.
Things you'll Need
- An intranet or internet site to host the Excel Web App on, or an HTML file you can email to your report recipients. Yep, that's right, you don't even need a website to distribute a report viewable in a web page that contains an embedded Excel Web App. More on that later.
- Excel - the full version on your PC. Note: there is an Excel Online version but it doesn't include all of the functionality of the PC version. Mind you, if your report is farily basic then you might get away with using the online version of Excel to build it.
- A OneDrive account to host your Excel file in the cloud. They're free. Note: OneDrive for Business accounts only provide iframe embed code. JavaScript embed code is only available with Personal OneDrive accounts.
- Some unsuspecting users to test your new Excel Web App toy on.
Step by Step Process for Publishing Reports on Excel Web App
Get Your Excel Dashboard Ready
This is all about securing your data in preparation for publishing it via the Excel Web App. If you don't need to tailor the view of your report for specific users, or you're not concerned about preventing nosey parkers from downloading the file then you can skip steps 1 to 3 in this section.
Note: I'm not going to teach you how to build the dashboard, since I do that in my course. This is how to get your already built dashboard ready for publishing via the Excel Web App.
Step 1: Setup Passwords
On the dashboard sheet cell D3 has been given a named range; ‘username’ and G3 has been given the name ‘password’. I use these names in my formula that verifies the password entered in the dashboard (more on that formula in a moment).
In columns A and B on my Validation sheet (see image below) I have my list of usernames and passwords formatted in an Excel Table called ‘security’.
Tip: make your passwords a bit more robust and less predictable than mine 🙂
In cell E2 (see image above) I have a logical test formula to check if the username and password combination entered in the dashboard sheet (in cells C3 and G3 respectively) matches the username and password combination on my Validation sheet:
=INDEX(security[Password], MATCH(username,security[Username],0)) =passwordIf they match the formula result is TRUE, otherwise it returns FALSE.
In English the formula reads:
INDEX the ‘Password’ column of the security table and return the password that is on the row which, MATCHes the name in the cell named ‘username’ on the dashboard, in the ‘Username’ column of the security table, match it exactly, if it = the value in the cell named ‘password’ on the dashboard worksheet then return TRUE, if not return FALSE
Every formula in my analysis is multiplied by the result of this formula. When you multiply by TRUE it is the same as multiplying by 1, and multiplying by FALSE is the same as multiplying by 0.
We all know that anything multiplied by 0 = 0 so if the password doesn’t match then the formulas all result in zero and nothing is displayed in the dashboard.
Remember: You don’t have to add this password protection step. If you don’t mind John seeing Bob’s reports and vice versa, you could just provide the drop down list and let each person choose their view, and if they want to view the other results then that’s ok too.
Step 2: Hide the password. Format cell G3 with custom number format ;;; to hide the password from view.
CTRL+1 to open the Format Cells dialog box > Number tab > Category; Custom > in the ‘Type’ field type in 3 semi-colons ;;; > press OK.
Step 3: Hide Sheets
After creating your Excel Dashboard you’ll want to hide any sheets that are confidential. This is an extra barrier to someone accessing the confidential data. It’s not strictly required since the only worksheet visible in the Web App will be the dashboard, but if someone were to find a way to download the workbook then this would be another hurdle for them to jump before actually locating the confidential data.
Usually we'd just right-click and hide the sheets we don't want accessed and then put password protection on the file to prevent them from being unhidden. However, in the Excel Web App we can't display a workbook that contains password protection so we’re going to use the VBA editor to change the sheet properties to ‘2 – xlSheetVeryHidden’, thus hiding the sheets.
Note: we use this approach because a VeryHidden sheet won't even show in the list of hidden sheets available for unhiding. i.e. when you right-click a sheet tab and select unhide. Plus you can’t access the VB Editor from the Web App.
- Alt+F11 to open the VBA editor.
- In the VBAProject list select the sheet you want to hide then below in the Properties change the ‘Visible’ property to ‘2 – xlSheetVeryHidden’:
You’ll notice as you change this setting the sheet will automatically be hidden in the workbook. Repeat for each sheet you need hidden then save the file. Note: you don’t have to save the workbook as a .xlsm since there is no VBA code actually in the workbook.
Step 4: Name Your Dashboard Range
Set a named range for your dashboard area. Select the cells where your dashboard resides and in the Name Box give the range a name:
This just makes it easy to specify the range you want visible in the Web App.
Step 5: Protect cells – this is optional but if you don’t want people to accidently break your report (you know they will) then it’s prudent to protect the cells they don’t need to edit. i.e. everything except the username cell and the password cell. You don’t need a password on this protection, simply turning it on in the Review tab > Protect sheet will do.
Step 6: Delete the Password - before you publish the Dashboard to your website you'll want to make sure the password in cell G3 of the dashboard is removed. Otherwise when the Excel Web App loads in the web page it will display the data for the currently selected salesperson. Just like is does in my example above.
Upload it to OneDrive
Now that your dashboard workbook is ready it’s time to upload it to OneDrive and get the JavaScript code for embedding the Excel Web App in your web page.
Step 1: get yourself a OneDrive account. They’re free and come with 5GB of data, in fact if you already have any kind of Microsoft account like Hotmail, Outlook.com, MSN, Office 365 etc. then you can go right ahead and access your OneDrive account.
Note: OneDrive for Business accounts do not have the JavaScript embed option, only the OneDrive Personal accounts do.
Step 2: Upload your Excel workbook to OneDrive.
If you have Excel 2013 or later, you can save it to OneDrive (no need to Upload):
Or in Excel 2010 go to the File tab > Save & Send > Save to Web menu:
Step 3: Locate your file on OneDrive and right click and select ‘Embed’:
Step 4: Click the ‘Customize how this embedded workbook will appear to others’ link:
Step 5: Customise what you want to show and how to show it:
OneDrive won't let you set the width to anything larger than 700. Who knows why, but you can manually alter this later if needed.
Step 6: Copy the JavaScript and paste it into your web page (see next step for creating your web page).
IMPORTANT: You must select the JavaScript code as opposed to the 'Embed code' (see last box in image above). If you use the Embed code the Viewer bar (the dark grey bar at the bottom of the Excel Web App) will still contain 2 icons which completely defeat the purpose of unchecking the 'include a download link' option. They are:
- Information about this workbook
- View full-size workbook
The problem with these icons is they enable anyone to download the workbook easily because if you choose to view the Web App in full-size it will include a button where you can then open the file in Excel.
Also, when you use the Embed code the 'Information about this workbook' icon gives a direct link to the file on OneDrive which you can then use to open the file in Excel.
The Embed code method does this despite the fact that we have opted to NOT include a download link. It's frustrating!
That's why you MUST choose the JavaScript code. For some reason the JavaScript correctly hides the 'View full-size workbook' icon, and the 'Information about his workbook' doesn't give the direct link to the file on OneDrive like it does if you choose the Embed code.
Create Your Web Page
To view the web app you need to create a web page in which you can put the code you get from OneDrive. You can put this code into a website hosted by your company for internal use (an intranet), or you can put it on a website accessible on the internet. You might need your web developer to help you with this.
Alternatively you can create a simple HTML file which you can store on your computer. The HTML file will open in your browser and works just as well, though probably won’t look as good as hosting it on your own website.
Download this example .html file, or right click the link and open in a new tab to see how it would look.
If you do use a HTML file to put your OneDrive code in, you can email this file to whomever you want to see your dashboard. It's no less secure than viewing it on a ‘regular’ website and it comes with the added bonus that the file size will be tiny since it only contains a few lines of code.
A basic HTML file is just a text file with some special codes in it. The example HTML file I provided above looks like this:
In order for you to get your embedded workbook working, you just need to replace the code I've highlighted in orange, with your own code copied from OneDrive.
Please note that I use the excellent Notepad++ for editing code including HTML. Notepad++ provides helpful features like coloring of code elements. You can use Windows Notepad but it's not as feature rich.
If you want to change the page title – this is what the title of the page will be in the browser - just change the text between <title> and </title>. I’ve given my page the title ‘Embedded OneDrive Excel Workbook Example’.
Save the file with your code and and open in your browser. You should be looking at an embedded Excel workbook.
If you have a scroll bar along the bottom of the embedded workbook like this
Then you could give the workbook more room on the page. In your code look for the bit at the top like this
<div id="myExcelDiv" style="width: 900px; height: 703px"></div>
and change the width value to something bigger. I’ve changed mine from 700px to 900px, and the scroll bar is now gone.
If you have a vertical scroll bar, adjust the height value.
Don’t worry, you can’t really break anything. If you change the value to something too big and the workbook now goes off the right-hand side of the page, just change the width value to something smaller and try again.
Hiding the Excel Web App Viewer Bar - Optional
Remember the viewer bar is the dark grey bar at the bottom of the Web App. I like to hide mine as I think it makes the report cleaner, but it's up to you.
The example HTML file above already contains the JavaScript code necessary to hide the viewer bar, so if you want it back you just need to remove this code.
Looking inside the example HTML file you'll see a bunch of code between two <script> tags, below the bit where you put your embed code, shown here highlighted in orange:
Delete all of this code, save your file and the viewer bar will be back.
Why This Works
What we've done with the JavaScript is tell your web browser to hide the Viewer Bar. It's still there, just not being displayed on screen.
We can do this because we know the ID of the Viewer Bar, which is ewaSyndmyExcelDiv_m_ewaEmbedViewerBar.
Web pages are made up of many elements like text, images, paragraphs and lots of other things 'under the hood' that aren't actually displayed on your screen. You can give each one of these elements an ID which allows programmers to manipulate the web page elements, which is exactly what we are doing.
By knowing the Viewer Bar's ID, we can do things to it, like hide it. But if the Viewer Bar's ID is ever changed, our JavaScript to hide it won't work. So, warning, if Microsoft change the ID of the Viewer bar, my JavaScript will not hide it.
If this does happen, just let me know and I'll try to find another solution to this.
Workbook Layout Problems in WordPress
We've had a number of people who are using WordPress report that when embedding a workbook, the formatting and layout of the workbook isn't right. It just 'displays weird'.
If you haven't had this type of issue, or you aren't using WordPress, you can skip this whole section.
After looking into this what I found is that the style sheets in WordPress could affect the way a workbook is displayed.
The look and layout of a website is controlled by the styles for that site. Things like the size of the font, the color of text, spacing between paragraphs (basically the entire site layout and look) are controlled by the site's Cascading Style Sheets, or CSS.
In these CSS files, I found that some WordPress websites had particular settings for the way a table is displayed that were affecting embedded workbooks.
You can think of a table in a web page as just a series of rows and columns, containing cells. Just like a workbook.
An embedded workbook is displayed on a web page as a table, so could be affected by any CSS for tables on the site it was embedded in.
The Solution to Incorrectly Displayed Workbooks
The solution to this, is to use a combination of iframes and the JavaScript embedding code.
OK, let's slow up a bit first. I know I've been talking a lot of web coder stuff : JavaScript, CSS, iframes. But it's really easy, just follow these steps and you'll be fine. But if you do have any issues just let me know, I'm more than happy to check over your web page.
Right, an iframe is one of those 'under the hood' elements in a web page. If you used the 'Embed Code' from One Drive, you've already used an iframe. An iframe allows you to load one webpage (let's call it the target) inside another web page (we'll call that the host). The web page in the iframe can't be changed by the CSS styles of the host website. It's just one of the rules of iframes, and it's very handy for us in this case.
Create your webpage following the steps above (use my example file) and you will have a .html in which you have your JavaScript embedding code. If you open this in your web browser you're workbook should load.
Now you need to put this web page onto a web server, either your own website's server or one internal to your company. You'll probably need to use FTP or the Control Panel provided by your hosting company to do this. Or if it's an internal server, ask your IT people to help. You will need to get the URL (link) to this web page. My web page is located at https://d13ot9o61jdzpp.cloudfront.net/files/moth_embedded_workbook_js.html
Back in your WordPress site you add the iframe and reference your own web page, using it's URL. If you wanted to load our example workbook you would enter this :
<iframe width="900" height="703" frameborder="0" scrolling="no" src="https://d13ot9o61jdzpp.cloudfront.net/files/moth_embedded_workbook_js.html"></iframe>
Notice how I have set the iframe width and height values to match the actual width and height I want the workbook to be.
Save your WordPress page/post. That's it. The workbook should now load, the viewer bar should be hidden and the workbook should display without any messed up layout.
Caveat
Now that you're ready to release your Excel Web App dashboard to your users please bear in mind that this is not bullet proof security. By its nature JavaScript is downloaded and run on the computer of the person looking at the web page, which means they can access the code and change it.
Anyone who knows enough JavaScript can edit the code and download your Excel workbook. And if they can do that then they can probably find a way to unhide the VeryHidden sheets too.
If you wanted to make it even more difficult you could obfuscate the code which would further complicate it by converting the JavaScript from ‘JavaScript English’ to ‘JavaScript Gobbledygook’, but again even that can be reversed using various online tools.
So remember, until Microsoft put some proper security in place you should only use this with documents that do not contain super sensitive information or anything that you really don’t want people to see.
More OneDrive Uses
OneDrive and the Excel Web App have a vast range of uses. I’ve just demonstrated one here, however I recommend you take a few minutes and click here to see what other things you can do with it.
Excel Online and Excel Web App are in an ongoing enhancement phase, so what you can’t do today, you might be able to do tomorrow. This post covers the new features that were added in Nov 2013.
Dan says
So the JavaScript is back, however, it’s different from before and the code from above doesn’t seem to hide the bar anymore. Any suggestions?
Philip Treacy says
Hi Dan,
My code is still hiding the Viewer Bar in the embedded example above.
Can you please open a Helpdesk ticket and send us your JS embed code/file so I can see what I can do for you.
Regards
Phil
Willem says
Hi Mynda. I can’t hide Excel Web App Viewer Bar because I don’t have the Javascript option when I ask Excel for the embed code. Is there another way?
Thanks
Willem
Mynda Treacy says
Hi Willem,
No, there’s no other way, sorry. You’ll just have to be patient and wait for Microsoft to enable the JavaScript option again. They’re working on it. Hopefully it won’t be too much longer.
Mynda
Willem says
Hi Mynda. Your embedded Excel tabel is nice and clean, in other words, it doesn’t show the excel task bar at the bottom. How did you manage that?
Great article!
Willem
Mynda Treacy says
Hi Willem,
In the Options you’ll find the settings. File tab > Options > Advanced > Display Options for this workbook.
Mynda
Aurélio says
Hello! The JavaScript button is gone. Some option for the embed to hide the bar? Thank you.
Mynda Treacy says
Hi Aurélio,
I think it’s a bug in OneDrive. I’ve notified Microsoft and am hoping they fix it soon.
Mynda
Federico says
Hi guys! We turn 2018 and Java script button is still away. Anyone has some information? I was look for a solution to hide the bottom bar….
Mynda Treacy says
Microsoft are working on a fix. I expect it will be a few weeks away yet.
Federico says
Thanks Mynda, Hope they’ll fix soon otherwise the sharing utility is useless. As you correctly explained with the incorporation link option users can reach the sheet from the bottom bar and download it. No matter if you say to not include a download link (it makes no sense!). So the Javascript option is the only way to protect the sheet from undesiderate sharing.
In the meantime i tried to hide this bottom bar with no success as the code come from an external source. I’d be glad to read if anyone found another solution.
Best Regards
Federico
Mynda Treacy says
There’s no other way that I’m aware of, sorry.
Oliver says
Hi…I am trying to do this and your instructions are great – but I am only getting the “Embed” option on the OneDrive interface in step 5; the JavaScript option is missing. Have tried to search as to what might be causing this, but to no avail.
Any ideas?
Mynda Treacy says
Hi Oliver,
Every now and again this option seems to disappear from the dialog box. I’ve raised it with Microsoft and hopefully it will be back soon.
Mynda
Jason Perez says
Hey There- Thanks for the article but I’ve tried to hide the bar and I can’t get it to work. Any chance you can take a look for me? Thanks so much.
Philip Treacy says
Hi Jason,
I’ve emailed you a working solution. Let me know if you don’t get that.
regards
Phil
Jason Perez says
Update: Phil figured this out for me. As someone who is just learning java this is so huge for him to take the time to help people. It’s really appreciated. I’ve learned a lot from you in a short time thanks for taking the time to put this stuff together.
Jason
Philip Treacy says
No worries Jason 🙂
Glad to help.
Phil
Brian Dang says
Whoa, you guys are still getting comments on this article–it’s certainly had a good impact!
I wanted to let you know about PowerApps. I’ve been building apps on it that show and filter data using familiar Excel formulas. I previously used your solution on this page for showing student grades behind a username/password. But I wanted more security since Microsoft still had not disabled the download button completely (I was still able to find a loophole with the workaround shared on this page).
I think PowerApps is right up your alley!
Philip Treacy says
Thanks Brian, I’ll take a look at Power Apps, but at the moment the site won’t even let me log in!
MS do need to improve the security for shared workbooks, and we do say in the article that the workaround for hiding the Download button can be circumvented by anyone with enough know-how.
Regards
Phil
Bax says
Hi Mynda,
I had an embedded file in a website which I did following your instructions. It is an interactive file that allows users to select from dropdown lists and enter quantities to calculate a freight cost.
It has been active for around a year now and worked fine up until a few days ago. Now the file displays ok but the user interactivity has gone.
Have you come across anything similar? I have tried recreating the embedded page again and can’t get it to work.
Thanks in advance.
Bax
Mynda Treacy says
Hi Bax,
I’m not aware of any reason for it to stop working from Microsoft’s point of view. Mine is still working.
Have you tried testing it in a different browser and a different PC? Has your website had any changes recently that would prevent interaction with it? I’d speak to your webside administrator as well.
Mynda
Bax says
Hi Mynda,
I have found what the issue is but I have no idea why it has happened.
In the embed code there is an option to specify the cell to start in:
uiOptions: {
showDownloadButton: false,
showGridlines: false,
selectedCell: “‘Calculator’!A3”
In my original web page this was set to C3 and as stated this has been working unchanged for over a year. The web page hasn’t changed and the Excel source file hasn’t changed.
Now the interactivity with the workbook will only work if the cell referenced is in column A.
I have created an html file. If I edit in notepad and change the cell reference to anything other than A it stops working. It is bizarre.
Regards
Bax
Mynda Treacy says
Good find, Bax. Thanks for sharing!
Ammar Yaseen says
Hi,
Great information. I was wondering if I can embed my macro enabled excel sheet in my web page? If this is not possible, can You suggest the best way I can protect my worksheet from being copied and forward?
Mynda Treacy says
Hi Ammar,
You can embed Macro Enabled workbooks/worksheets in the WebApp but you can’t execute VBA on the web. I write about Excel worksheet protection options here.
Mynda
Orin says
This is a great tutorial. I am having an issue when my dropdowns contain more than 8 items. It seems like when I scroll and click on the 9th+ item; the next time I try to dropdown the menu it gets stuck.
has anyone else come across this issue when using embedded excel files for MS excel online?
Mynda Treacy says
Hi Orin,
I haven’t come across this before. Have you tested this in a different browser?
Mynda
Joshua Dela Cruz says
Hi!
Although I haven’t got to try that solution yet, I already wanted to ask if that will work if I want my worksheet protected except certain cells. For example:
Auto fill data if: A3:A4 – so A3 and A4 will be editable.
Thanks!
Catalin Bombea says
Hi Joshua,
If you protect the sheet, only the unlocked cells will be editable, of course, as described in this article. What do you mean by “Autofill data if…”? There is no conditional protection in excel, only with a macro you can do such thing, but not online.
Catalin
Joshua Dela Cruz says
Thank you for replying on my question, but I already figured it out. But there is one last thing I want to know, how to print them if they are in javascript or iframe?
Catalin Bombea says
I believe you are referring to printing a range of cells from an embedded workbook. Never tried it, but I think you cannot do that with browser print tools, you can print ranges only if the workbook is opened in Excel Online, using the OneDrive print tools, not browser Print tools.
Catalin
Joshua Dela Cruz says
Yeah that’s what I think, but what if I make my excel file as a webpage will it be published with macro/buttons?
Catalin Bombea says
No, macro’s and ActiveX controls are not working in browser.
Joshua Dela Cruz says
Hello! One last thing! How can I embed many excel files without the viewbar because I cannot managed to show many excel files without them having viewbars, simply I want to remove all the viewbar using the javascript not iframe.
Catalin Bombea says
You do have the instructions in this page, you just have to read it: hide viewer bar
Cheers,
Catalin
Joshua Dela Cruz says
Sorry if there is a miscommunication there, but the thing there is I can only make one sheet with invisible viewbar. My problem is that how can I make or embed many excel files with no viewbars viewable?
Thanks 🙂
Joshua
Philip Treacy says
Hi Joshua,
We need to identify the Viewer Bars so that we can hide them. At the moment the JavaScript code uses the Viewer Bar’s ID, but each element on a page must have a unique ID, so the next Viewer Bar’s ID should be different.
So we would need to identify/select the Viewer Bars another way, most likely by their CSS class. I can have a look and try to provide you with an answer. Do you have a web page with multiple sheets embedded that I can look at?
Regards
Phil
Joshua Dela Cruz says
Hi Phil,
This is my webpage for all of the excel files needed to show on the web. Sorry I can’t really managed to make a success with that. That’s why I only included 2 excel files and shows nothing.
nobleplacemegaworld.com.ph/reservation/excelfiles.php
Real thanks 🙂
Philip Treacy says
Hi Joshua,
Here’s your 2 workbooks without viewer bars.
Joshua’s WB’s
regards
Phil
Joshua Dela Cruz says
Hi Phil,
Oh so that is how it is, but I can’t understand that the two excel workbook is the same.
That’s great 🙂
Philip Treacy says
I’ve used the same fileToken for both workbooks. Just change this to the fileToken of the other workbook(s) you want to embed.
Phil
Andrew says
Hi,
Thanks for this great piece of information. However, none of the methods working for me with my WP site, even though the .htm files, if opened from my folder, shows with no problem. The page only shows the code text, but the coding doesn’t seem to be working.
Regards,
Andrew
Philip Treacy says
Hi Andrew,
If you can provide a link to the page on your site with the embedded workbook, I’ll take a look and try to figure out what is going on.
Regards
Phil
Robert says
Thanks for this information! It is very helpful.
I’m hoping you can help me on this issue I have. When I embed an excel file, users are still able to click and edit locked cells (although this is followed by a message stating the cell is locked). Is there a way to prohibit clicking on locked cells altogether? Will using Javascript solve this.
Mynda Treacy says
Hi Robert,
You can’t prevent people clicking on the cell and attempting to edit it. Obviously if you’ve prevented editing then what ever they type doesn’t remain and they get the warning.
Mynda
Robert says
Thanks for the response. One more question, do you know a trick that will scale to fit the embedded excel sheet to the frame rather than stretching out the frame to fit the excel sheet. Maybe adjusting something under the “”? Many thanks
Mynda Treacy says
Hi Robert,
That’s a good question, but I’m not aware of a way to do this. I don’t recommend scaling charts etc. down because they often don’t render properly when in Excel, and I suspect it would be worse on the web. Better to build your reports at 100% zoom and make the charts etc. smaller.
Mynda
Nico Troostheide says
What a great tutorial!
Searched the entire internet on hiding the lower excel bar: NOTHING! Except here!
And it works great!
Thanks a lot!
Mynda Treacy says
Glad we could help, Nico 🙂
Nico says
Hi Mynda,
Having used above wonderful solution for some time now on our website, I now get the need to use the same solution, but now with the possibility for users to actually save entered data in a list.
However, until now I see only one way to interactively share an excel list by sharing the whole workbook with a link to OneDrive.
I hope there is a solution for this, in order to be able to maintain the same way of working.
Best regards,
Nico
Mynda Treacy says
Hi Nico,
Unfortunately there is no way to enter data using the Excel WebApp. You might like to look at the Excel Survey tool instead: https://www.myonlinetraininghub.com/excel-surveys-an-easy-way-to-collect-data
Mynda
Nico says
Hi Mynda,
Thanks a lot for your quick response!
However your answer crushed my hopes…..
I will check out the survey tool and maybe that could ease the pain 😉
Nico
Mark says
Hi Mynda,
I have just come across your article. It is really helpful especially the bit that allows you to hide the viewer bar. I have been looking for a while how to do this.
I have another question that I am hoping you can help with. I have created a webpage with an embedded workbook. The workbook allows users to input quantities against products and it calculates the shipping costs. It all works ok but one thing that it doesn’t allow you to do is edit cell values once entered. You can overwrite them with another value but you can’t delete or edit the entry. All of the other interactivity works fine.
I have been searching online for an answer but can’t find anything. Hoping you can shed some light on this.
Thanks
Mark
Mynda Treacy says
Hi Mark,
Glad you found this article useful.
As for your problem with not being able to delete the contents of a cell. I’d say the behaviour you’re experiencing is by design. The settings allow users to ‘type into cells’, it does not say ‘allow people to delete’. If you want to edit what is in the cell you just type into it again. If you want to effectively delete what you’ve typed then you’d have to enter a zero.
Kind regards,
Mynda
Mynda
Nathan says
Hi Mynda
Thanks for the post – this is by far the best resource I have found on embedding excel files.
There’s just one thing that I can’t figure out. How did you get your drop down boxes to work? My excel workbook contains drop down lists created through “Data Validation” but when I embed this on my website – or even just viewing the file in the browser the drop down boxes aren’t working.
The drop down boxes work perfectly on your example – I’ve briefly tried creating drop down lists using “Form Control Combo Box” but didn’t work that way.
Any help is much appreciated.
Nathan
Catalin Bombea says
Hi Nathan,
Can we see a link to that workbook? Data validation lists should work in browser, ActiveX form controls are not allowed in OneDrive.
Catalin
Nathan says
Hey Catalin
Thanks for your reply (sorry I just saw it now)
Here’s the link to a page where I have added the I-frame embed where the workbook can be downloaded – http://testitout2.siterubix.com/sample-page
The drop down lists don’t show on excel online either – only on excel on my computer.
Thanks for your help,
Nathan
Catalin Bombea says
Hi Nathan,
It looks like you have used the default code generated for embedding. You have to personalize the way you want this to show, use the link: Customize how this embedded workbook will appear to others, and in Interaction section, make sure you “Let people type into cells” , selecting a value from a dropdown is exactly the same thing as typing into cells, if typing is not allowed, dropdowns are not active.
All these settings are explained in the article, please read it again more carefully, there are other useful settings you should be aware of.
Hope this solves the problem 🙂
Cheers,
Catalin
Nathan says
Thanks so much Catalin. That fixed it! Appreciate it.
Catalin Bombea says
You’re welcome 🙂
Ephi says
Great article and so very helpful! Thank you for this.
I am just about creating a blog on WordPress and intend to have interactive calculators and financial models (already have created in Excel) which users can input values and see output charts and results. From this post, it seems I could simply use the Excel web app (hurrah!). My questions though are:
– Is there any downside to this method? Do you consider it a robust solution?
– Would the calculators work fine when viewed on a mobile device or tablet?
Thank you
Philip Treacy says
Hi Ephi,
For the most part, if it works in Excel then it should work on the website, however there are somethings that aren’t supported in the Excel web app like shapes and VBA.
As you will be creating the models in Excel, it saves you having to create them in the web page using JavaScript for example, so that is a big win.
Microsoft do seem to have a history of making changes (unannounced) that can affect the display of the embedded workbook, and at worst they have broken workbooks in the past. So is it robust? Yes, as long as Microsoft don’t break it on you 🙁 The best you could do is to monitor the page(s) where your embedded workbooks are and check that they continue to work. This may not be the best answer though if your business is relying on these calculators to be working for your customers/visitors.
With regards to tablets and mobile devices, if the workbook is embedded in an iframe with a set width and height, if the device can’t display that width/height, then some of the workbook won’t be visible. There may be ways around that but you’d need to speak to your web designer about this.
Regards
Phil
Ephi says
Thanks very much for the response, clear explanation! 🙂
Philip Treacy says
No worries 🙁
Shayne says
You are literally my Excel Webapp god! I have been using embed Zoho Sheets for years because I could not prevent the downloading of the Excel Webapp. Thank you so much, I am thankful that I came across your site.
Philip Treacy says
🙂 Thanks Shayne. But please remember that this is not bullet proof security. Anyone with a little JavaScript knowledge or who knows how to use the browser’s developer tools can get your document.
Regards
Phil
Shayne says
I did catch that but my information is far from confidential or critical. I just didn’t want the download button showing up.
Sort of like locking the door to my house, if anyone is motivated enough they will still get in. 🙂
I do appreciate this post!
Philip Treacy says
Cool Shayne. Just wanted to make sure you knew this ‘protection’ wasn’t too hard to circumvent 🙂
Regards
Phil
SS says
I’m trying to understand exactly how difficult it would be for someone to steal my file. Would anyone with knowledge of javascript able to steal it? Is there any way to prevent that? Basically any programmer?
I really don’t want anyone stealing my file. Just want them to interact with it. What if there was another host in between? I’m no programmer, just been using computers for 25 yrs. Or how about if there was some type of self destruct if opened on a computer? Any out of the box ideas?
I am not familiar with Zoho or Caspio and it would take me a while to figure those out. Plus I think I can design it more nicely in Excel as you have.
Thank you for any ideas.
Mynda Treacy says
Hi SS,
Yes, anyone can get your file and no, there’s no way to protect it using the WebApp.
Kind regards,
Mynda
nina says
I would really really REALLY love to hide the ugly excel navigator bar on the bottom, but can’t make it work. I have read the comments below, and found additional coding for the ewa, but I’m afraid I’m still a bit dumb when it comes to coding. Everything disappears. Can you please help?
Philip Treacy says
Hi Nina,
Please open a Helpdesk ticket and attach your code/HTML page to the ticket. I’ll take a look and see what I can do.
Regards
Phil
shahmadbd says
Thanks you mate, you’ve tried a lot… but Microsoft has simply become rubbish in terms of even to care about document security… for last several hours I’ve been trying my level best to keep a document that I developed secured after embedding this way or that way…. all ways failed to keep the document secured; your way was close, but very easily can be breached, and it’s entirely a neglecting issue by the arrogant microsoft… 🙁 🙁
Now I’m not even sure if there’s any single way to keep the interactivity & simultaneously maintain document security on office document or microsoft’s sites/servers…. really saddening & tragic!!
I said your way was close enough, because it just took some minutes to formulate that link↓ of yours….(below), fully downloadable document. Sorry as I was testing myself for hours….and failed to find a solid solution!! 🙁 🙁
Philip Treacy says
Hi,
Yes as we have said in the post, our approach doesn’t really provide security as anyone with some JavaScript knowledge can find a way to get the embedded workbook.
Unfortunately Microsoft haven’t addressed this issue, we can only hope they get around to doing so, sooner rather than later.
Phil
shahmadbd says
True. And I certainly Thank You for your Wonderful works, really excellent. So many great learning articles…
And I love Excel & similar analyses, one of my most favorite works, but what microsoft is doing these days, is really depressing….this thing has been an issue at least for past 4-5 years now, yet they didn’t bother to fix it. Unfortunately, Open Office distros also don’t have as many features so far…tableau or google sheet also sometimes very limited, unless something heavier software can be used…. What do you think…do you think it’s time to switch to a substitute that provide all those Excel features and more…? …Any strong Recommendation??? Thanks a lot again… 🙂
Philip Treacy says
Thank you.
Sorry, I couldn’t offer an alternative to Excel that does the job of securing the document any better. Hopefully Microsoft will get around to addressing this very soon.
Phil
Cristian Firmino says
That very good article, I’m looking for this for long time, your website is very amazing, I am doing a Excel website for the brazilian public and here I am learning many new things.
Thank you
Mynda Treacy says
Thank you, Cristian 🙂
Tom says
Hi,
Thank you for this article I think it`s a great simple solution for Interactive Dashboards.
Though, I don`t understand how the dashboard changes when a the username entered and the password match.
So I`d like to know where in the process shown above is this done and how.
Plus, I`m confused about some steps.
In Step 3: Hide Sheets, in order to only show the Dashboard sheet on the web page we should “change the ‘Visible’ property to ‘2 – xlSheetVeryHidden’:” for every sheet in the workbook except the Dashboard sheet right ?
But then how do you change the Dashboard to make it display graphs and tables after Bob or Richard entered his password ?
In Step 4: Name Your Dashboard Range, the Dashboard range would include Bob’s Sales and Richard’s Sales right ?
So again, how do we select the right data to be displayed to Bob or Richard ?
Thank you for your response,
TFM
Mynda Treacy says
Hi Tom,
In Step 3; correct, we only display the actual Dashboard worksheet.
In Step 4 I use a INDEX & MATCH formulas to lookup the data for the person selected in the data validation list. If the correct password is entered the the data is displayed. Here is a tutorial on INDEX & MATCH.
I cover the steps for this dashboard in detail in my Excel Dashboard course.
Mynda
asmaaelkeurti says
Why is the embedded excel sheet in this page not interactive any more?
Mynda Treacy says
Microsoft have broken it! They’re working on a fix as I type. Please check back later to see if it’s working.
Mynda
Brian Dang says
It’s working again 🙂
Philip Treacy says
yayyy 🙂
Kah Hoe Loi says
Thanks for the article. It was really hepful.
However, I can only get mine to work if I use the Embed code but when I use JavaScript, all I get is a blank page with some of the JavaScript code at the bottom of the page.
Was wondering if you know what could be causing this.
Thanks
Kah Hoe
Mynda Treacy says
Hi Kah Hoe,
It’s difficult to debug it without seeing it. Can you please send us the HTML file, or a link to the page on the web where your WebApp is embedded, via the help desk.
Thanks,
Mynda
Steve Parton says
Hi Mynda and Philip,
I just got back to this, and saw Igor’s additions and Philip’s WordPress findings and it now works fine – all as of a few minutes ago (so still some tidying up to do):
http://cblasia.org/cash_is_king/
Thanks very much for your considerable effort over a long period on this.
Cheers
Steve
Mynda Treacy says
You’re welcome, Steve.
Steve Parton says
Hi Mynda and Phil,
Has something just changed in onedrive? My embedded spreadsheet no longer shows and neither does yours!
Cheers
Steve
Philip Treacy says
Hi Steve,
Our workbook looks fine to me. Maybe you’re experiencing an issue due to your location. I have encountered regional issue before as the JavaScript required to do the embedding is served form several servers around the globe.
If you can open a Helpdesk ticket and send me a screenshot of what you are seeing, both with your workbook and ours, and let me know your location, I’ll take it up with Microsoft.
Regards
Phil
Steve Parton says
Hi Phil,
No, its ok, I can see them all again now! I have 2 separate sites with embedded spreadsheets plus your example, and I could not see any of the them this morning, and then they all came back a little while after I sent you the message..who knows!?! If it is regional I suspect we are in the same region, so perhaps it all happened while you were having breakfast 😉
If I see it again I will take a screen shot.
Thanks and regards
Steve
Philip Treacy says
OK no worries, glad it’s all back and working 🙂
Phil
Brian says
It looks like they updated the javascript for spreadsheets. There’s an area that includes:
/*
* Add code here to interact with the embedded Excel web app.
* Find out more at {3}.
*/
Philip Treacy says
Hi Brian,
Yes they have changed the JavaScript code to include these extra comments, but you could always interact with the workbook by writing your own code.
Not sure what they mean by ‘Find out more at {3}’ though as I can’t find {3} anywhere.
Phil
Igor Markovic says
Occasionally I’m still getting some render issues when using JavaScript to embed excel sheets from OneDrive. I’ve tried to get some support in this, but after literally hours spending on de telephone with MS and posting in about 5 forums I still didn’t get in contact with someone of MS that knows more about this API. It looks like nobody knows exactly who develops this script and should be able to help. Maybe someone here knows where/who to ask?
Philip Treacy says
Hi Igor,
I’ll email you directly and see what we can do to help.
Cheers
Phil
Igor Markovic says
I noticed that the code for hiding the toolbar wasn’t working when multiple ewa controls were loaded. So I’ve updated the code to make it work in that scenario. This version of the function also doesn’t hide the complete bar as this will leave an ugly white bar below the workbook. Instead it just hides its content. This looks a lot prettier I think. This time I used the jQuery library to make the code more compact, however the same can be achieved without jQuery although the HideViewerBar would have to be rewritten. To include jQuery support on the page you can add the following script reference:
The hideViewerBar function is responsible for looking up the viewerbar of the ewa object and hiding its children. I noticed that the start of the ID changes (ewaSynd1, ewaSynd2, etc) in the case that multiple ewa controls were loaded. So this code tries to find the controls with an ID that end with _m_ewaEmbedViewerBar.
function HideViewerBar(ewa)
{
var iframe = $(‘iframe’, ewa.getDomElement());
$(“[id$=’_m_ewaEmbedViewerBar’]”, iframe.contents()).children().hide();
}
Calling the function when a workbook is loaded:
function onEwaLoaded(asyncResult)
{
if (asyncResult.getSucceeded()) {
var ewa = asyncResult.getEwaControl();
HideViewerBar(ewa);
}
}
This code can be freely used/shared without any restrictions or obligations 🙂
Philip Treacy says
Great work Igor, thank you.
Phil
Joshua Dela Cruz says
Sorry for the question but I would like to ask where should I put that function? At the head or the body itself and change the existing code like this?
function onEwaLoaded(result)
{
if (result.getSucceeded())
{
var iframe = document.getElementById(‘myExcelDiv’).children[0];
var innerDoc = iframe.contentDocument || iframe.contentWindow.document;
innerDoc.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = ‘none’;
}
}
Do I have to change this?
Philip Treacy says
Hi Joshua,
That function goes at the bottom of the HTML file before the closing </html> tag.
If you look at the sample HTML file I’ve provided, and look at the source code, you’ll see the function towards the bottom of the file.
https://d13ot9o61jdzpp.cloudfront.net/code/moth_embedded_workbook.html
You can use this file as a basis for your own workbooks. If you are having trouble getting the viewer bar hidden, please set up a web page I can access and I will have a look for you.
Regards
Phil
Joshua Dela Cruz says
Hi Phil,
Would that support multiple embedded excel files?
Thanks 🙂
Philip Treacy says
Yes 🙂
Here’s my example with 2 workbooks
https://d13ot9o61jdzpp.cloudfront.net/code/moth-multiple-embedded-workbooks-jquery.html
I’ve used some code provided by Igor and modified the page a bit. Each workbook is in its own div with a unique ID. The JS in both div’s call the function onEwaLoaded which in turn calls HideViewerBar.
The HideViewerBar function uses the jQuery library to hide anything that has an ID ending in _m_ewaEmbedViewerBar
For every additional workbook, copy/paste the block of code between <!– WORKBOOK 1 –> and <!– END WORKBOOK 1 –>. Then change the div id and function names to be unique.
Cheers
Phil
Igor Markovic says
I came across your site as I was looking into the problems we are seeing when displaying embedded excel sheets as the content is sometimes displayed incorrectly. Till now I haven’t found what is causing this. It happens at different machines, but not always at the same time. Sometimes one sheet is working correctly while the other is not. It even makes a difference if I’m logged in to onedrive or not. A really strange problem. At first I thought only JS embedded sheets were affected, but today I’ve also seen the iframe embedded sheets to be affected.
For people looking on how to hide the Excel viewer bar. Here some code you can use. The difference is that the sheets are now embedded into an iframe even when JS is used to embed the sheet and that you cannot access the element directly, but have to use the iframe content to get a reference to it:
function onEwaLoaded(asyncResult)
{
if (asyncResult.getSucceeded())
{
var iframe = document.getElementById(‘myExcelDiv’).children[0];
var innerDoc = iframe.contentDocument || iframe.contentWindow.document;
innerDoc.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = ‘none’;
}
}
Hope this will help some people. Cheers
Philip Treacy says
Good work, thanks for that Igor. I’ve added your code to mine above and listed you as the creator. Hope that’s ok.
Cheers
Phil
Dave Caban says
Thanks Igor. I’m a little confused on exactly where in the script I would paste that code in and if I would need to overwrite any of the original embed code. I’ve never used java so any guidance would be appreciated. Thanks!
Pavel says
I have new trouble with embedded excel file.
1. I make new folder ‘BACKUP’
2. I copy shared (embedded) file to new folder by copy-paste, then rename it as well.
3. After that my ORIGINAL excel file has removed share link
4. But my NEW cpy has OLD ID !
So, I should share OLD file again with new ID !
I hope, programmers in Microsoft will know about that and fix it.
Philip Treacy says
Hi Pavel,
Sorry I don’t know why this would happen. Hopefully Microsoft will fix this issue too.
Regards
Phil
sitanshu gupta says
Hi, if I use VBA to make my dashboard and upload it on one drive, do the VBA codes still work online as well ?
Thanks for the post !
Mynda Treacy says
Hi Sitanshu,
No, VBA cannot be executed on the web but the code will remain intact, so if you download the workbook from OneDrive to the desktop you can execute it there.
Mynda
Sam says
Hi,
Any update on the embed viewer bar? The ID (ewaSynd1_m_ewaEmbedViewerBar) has not changed but the hide javascript code is not working.
Thanks
Sam
Philip Treacy says
Hi Sam,
Microsoft have told me that they are working on it, so as of now, May 1st 2015, the issue still exists.
Regards
Phil
Pavel says
Hello,
here are two new problems:
1) Vertical Scrollbar not hidden
2) Horizontal Viewer Bar not hidden
You can see it on your page as well.
How to solve?
Philip Treacy says
Hi Pavel,
It looks like the JavaScript from Microsoft’s servers is broken.
I get 100’s of errors in the my browser’s JavaScript console saying ‘Refused to load unsafe headers’ with the net result being that my JavaScript to hide the Viewer bar doesn’t work, and this is also causing the vertical scroll bar to appear
We’ll try to raise this again with Microsoft. I’m guessing they’ve made some changes and broken this by mistake.
Regards
Phil
rob parkin says
Hi
Thanks for the article! You mentioned that Microsoft may at some point change the id of the ViewBar … looks as if they have as the code line below no longer works:
document.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = “none”;
Is it possible to find the new id, or is there another way to turn off the display of this? Also, is it possible to turn off the display of the vertical scroll bar that now shows in the embedded file?
Thanks
rob
Philip Treacy says
Hi Rob,
I’ve looked into this and the Viewer bar ID hasn’t changed, it looks like there is something broken in the JavaScript Microsoft is using to serve up the workbook.
When I load an embedded workbook page, I get 100’s of errors in the my browser’s JavaScript console saying ‘Refused to load unsafe headers’ – but the net effect is that my JavaScript to hide the Viewer bar doesn’t work, and this is also causing the vertical scroll bar to appear
We’ll try to raise this again with Microsoft. I’m guessing they’ve made some changes and broken this by mistake.
Regards
Phil
Sait says
Hi Phil,
thx for past helps. I have new issue. I hope that you recommend a solution.
I loaded Excel which has protected work sheet except certain cells for users to enter their number. Somehow Excel in website is having lock
symbol on the tab. I can’t edit the cell which should be overwritten.
any idea of removing the protection.
before i load it on OneDrive, It is working.
thx in advance
Regards
Sait.
Philip Treacy says
Hi Sait,
Can you please open a Helpdesk ticket and send me the workbook, and a link to the webpage where you have the workbook embedded.
Cheers
Phil
Ben E says
Hello, great article! Do you know how I can achieve the following; Setup a spreadsheet on OneDrive, protect the cells that shouldn’t be edited, and then make available in a simple html for the public to update the edit values. Do it through HTML and not the Online Excel function, and have changes written back to the excel document. This means the excel document is public, visible without all the buttons around it (like the remove download java script).
It seems to be the bit missing between an Online Excel Form which writes a new row each time, compared to showing the same row of data on a form that can be amended. Google online forms is close as it lets you edit your response, same row saved in the spreadsheet, but unfortunately limited to the form view and not excel view.
Any ideas?
Mynda Treacy says
Hi Ben,
You can use Excel Survey to do that:
https://www.myonlinetraininghub.com/excel-surveys-an-easy-way-to-collect-data
Mynda
Pavel says
Hello,
I have a new problem.
It looks like https://www.dropbox.com/s/o8cfsp6coa349n1/IMG_27022015_122116.png?dl=0
Some users can see it without problems. But some users can’t.
This page: http://dnatrade.net/index.3.html.
I thought it is because of regional settings on my MS account.
So, I tried to change settings there.
But It doesn’t help in my case.
What can I do in this situation?
Philip Treacy says
Hi Pavel,
I tried the link and it worked for me in Firefox, IE and Chrome.
What do yo know about the people who can’t see the chart? What do they have in common? Are they in a similar geographic area? Same browser?
There was a recent problem displaying embedded workbooks for people who were in the Asia/Pacific region and loading the JavaScript they needed to display the workbooks, from Singapore. But that has been resolved.
Sorry, as it’s working or me I can’t shed any light as to what is wrong for other people.
Regards
Phil
Pavel says
Thanks for reply.
Today it is working well.
One day works, one day doesen’t.
If it will be with an error again, then I’ll write again.
Now can’t show =)
Maybe in MS somebody does something with it every day.
Thanks.
Philip Treacy says
Hi Pavel,
Maybe! Hopefully whatever the cause of the problem it has been rectified and won’t return.
Regards
Phil
Pavel says
The problem is still here.
I think, it can be because of date format.
Actually, me and most of users have Russian date format (dd.mm.yyy) inside OS, but different OS language sometimes.
By idea, it doesen’t matter. But in fact, it does diagram unreadable for some users.
Now, if you open my page, you can see date format there in US format (mm.dd.yy).
Not only for you, but for all users. Right?
How can I change date format to Russian?
I have already Russian format in my Windows settings, Excel settings and on my MS account too.
But anyway, if somene open this page, they can see US date format on diagram.
And diagram doesent work for some users.
How to fix it? Maybe I’m wrong?
Philip Treacy says
Hi Pavel,
It would appear that your own regional settings within One Drive may affect the way the dates are displayed.
Our own regional settings in One Drive are set to Australia, Brisbane and we were still seeing US date formats on your chart.
So try this.
1 Log into One Drive
2 Click on your name > Edit Profile
3 In your Microsoft Account go to ‘Basic Info’
4 Change your Personal Details so your Country/region settings are correct.
Let me know if that works for you.
regards
Phil
Tomas Fransson says
Hi there! 🙂
Great article! I’m trying to embed an Excel spreadsheet on a WordPress page with the JavaScript version from OneDrive (to be able to hide the ViewerBar.) I have copied and pasted the JavaScript version into the text editor. I’ve also installed the Raw HTML plugin, and enclosed the code with the “Raw” markers.
As a standalone htm-file the spreadsheet looks & functions great. However, when I try to embed the same code on a WordPress page the spreadsheet displays differently. Extra white space is added above, to the left and right of the spreadsheet area. Some of the cell formatting seems to be lost too. Not pretty.
I’m a total newbie to JavaScript, so I guess I’m missing something obvious. Will you be able to help?
This is the code pasted into the blog post:
/*
* This code uses the Microsoft Office Excel Javascript object model to programmatically insert the
* Excel Web App into a div with id=myExcelDiv. The full API is documented at
* http://msdn.microsoft.com/sv-SE/library/hh315812.aspx. There you can find out how to programmatically get
* values from your Excel file and how to use the rest of the object model.
*/
// Use this file token to reference The Bottom Line of Commenting.xlsx in Excel’s APIs
var fileToken = “SD8CC26DD2D8CDBBE9!138/-8303954010569851927/t=0&s=0&v=!AI1DHVvG7vwfVJ0”;
// run the Excel load handler on page load
if (window.attachEvent) {
window.attachEvent(“onload”, loadEwaOnPageLoad);
} else {
window.addEventListener(“DOMContentLoaded”, loadEwaOnPageLoad, false);
}
function loadEwaOnPageLoad() {
var props = {
item: “‘Sheet1’!A1:F26”,
uiOptions: {
showDownloadButton: false,
showGridlines: false,
showParametersTaskPane: false
},
interactivityOptions: {
allowParameterModification: false,
allowSorting: false,
allowFiltering: false,
allowPivotTableInteractivity: false
}
};
Ewa.EwaControl.loadEwaAsync(fileToken, “myExcelDiv”, props, onEwaLoaded);
}
function onEwaLoaded(result) {
/*
* Add code here to interact with the embedded Excel web app.
* Find out more at http://msdn.microsoft.com/sv-SE/library/hh315812.aspx.
*/
document.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = “none”;
}
Philip Treacy says
Thanks Tomas.
If your stand alone page is working ok, It sounds like the problem is in WordPress. It may be that the theme you are using is affecting the layout of the embedded workbook.
Can you provide me with a link to the embedded workbook on your site?
Cheers
Phil
Tomas Fransson says
Hi Phil! Sure! On this page you can see how the Embedded and the JavaScript version displays differently: http://www.flauntmydesign.com/test-page
This is the exact code from the above page:
/*
* This code uses the Microsoft Office Excel Javascript object model to programmatically insert the
* Excel Web App into a div with id=myExcelDiv. The full API is documented at
* http://msdn.microsoft.com/sv-SE/library/hh315812.aspx. There you can find out how to programmatically get
* values from your Excel file and how to use the rest of the object model.
*/
// Use this file token to reference The Bottom Line of Commenting.xlsx in Excel’s APIs
var fileToken = “SD8CC26DD2D8CDBBE9!138/-8303954010569851927/t=0&s=0&v=!AI1DHVvG7vwfVJ0”;
// run the Excel load handler on page load
if (window.attachEvent) {
window.attachEvent(“onload”, loadEwaOnPageLoad);
} else {
window.addEventListener(“DOMContentLoaded”, loadEwaOnPageLoad, false);
}
function loadEwaOnPageLoad() {
var props = {
item: “‘Sheet1’!A1:F25”,
uiOptions: {
showDownloadButton: false,
showGridlines: false,
showParametersTaskPane: false
},
interactivityOptions: {
allowParameterModification: false,
allowSorting: false,
allowFiltering: false,
allowPivotTableInteractivity: false
}
};
Ewa.EwaControl.loadEwaAsync(fileToken, “myExcelDiv”, props, onEwaLoaded);
}
function onEwaLoaded(result) {
/*
* Add code here to interact with the embedded Excel web app.
* Find out more at http://msdn.microsoft.com/sv-SE/library/hh315812.aspx.
*/
document.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = “none”;
}
Gerrie says
Amazing article!! Exactly what I needed! that a million! However I could not embed the java code to hide the download bar. Only the iframe works as the javascript does not display.. Guessing the problem is on onedrive’s side since I used html editor to import the code exactly.
Any ideas how to remove the download option?
Again thanks a lot!
Philip Treacy says
Thanks Gerrie 🙂
I’m getting similar issues, I’ll check into it and get back to you.
Phil
Steve says
I had a Javascript embedded excel range in a webpage and it stopped working, but it was on WordPress and I think
a new WordPress version might be the problem. I was using a text control plugin to stop formatting..
Beau says
Hi Steve,
Did you manage to get it working again? Was it a WordPress issue or MS embedded javascript issue?
Steve says
Hi Beau,
Have not gone back to it in a few days, but will shortly, so still not working at the moment. I could still always get it to work just
using one drive and a browser as per the original post, just WordPress site stopped working with the embed.
I did however always need the wordpress ‘text control’ plugin to get around javascript issues related to formatting in wordpress.
Then my site WordPress version was updated, and the excel range would no longer appear so I presumed that
the plug-in needed a fix for the new wordpress version, which had/has yet to be done… but open to all other suggestions!
Beau says
Sorry one more question, from your experience what is the best way to have an interactive excel spreadsheet, but not permit access to download the file? The best solution for me was embed javascript (which I think has been removed), although you rightly pointed out, this can still be downloaded.
Can there be another solution where the document is linked to another excel file that sits on sharepoint, but the interactive excel file returns the formula?
Kind regards
Beau
Mynda Treacy says
Hi Beau,
I don’t know of any other way to prevent download of the file so your linking idea would be worth investigating.
Mynda
Beau says
Hi,
I had a JavaScript embedded excel in a webpage but it no longer works. It was working in the morning but it has since stopped. From what I can gather MS has stopped the JavaScript option as of a couple of days ago. Can you confirm that this is the case and I’ll have to use ifame 🙁
You help is much appreciated.
Mynda Treacy says
Hi Beau,
I’m not aware of that. My dashboard above is still working.
Have you checked the file is still on OneDrive?
Mynda
Beau says
Yup the file is still there, by the looks of it they have stopped the javascript code, the embed (iframe code) – which your is still works – the only problem is the spreadsheet can now be downloaded.
Excel Fiend says
Hello Mynda,
This type of functionality seems to be exactly what I am looking for. I have a couple of queries though, which I was hoping you could help me with:
* If I make available an Excel spreadsheet to the public, how is concurrent access handled? For example, if I publish a mortgage calculator and have two users accessing at the same time, isn’t there a possibility that one will be changing, say the interest rate, while another is changing the principle? Does each user get their own environment i.e. their changes are local to them?
* How does it handle VBA? Do these all work? If so, what about Macros that take a long time to complete (say 30 minutes). Is this processing all done on Onedrive?
Thanks in advance for your help!
Philip Treacy says
Hi Simon,
Concurrent user access is handled by the Excel web app and according to this article http://blogs.office.com/2013/11/06/weve-updated-excel-web-app-whats-new-in-november-2013/ it would seem that users can simultaneously access and change the same workbook/sheet.
So in your example, it would appear that 2 people could affect each other as they use the workbook. You could test it to make sure, but you may be better off using JavaScript to achieve what you want.
The Excel web app doesn’t support VBA 🙁
Cheers
Phil
Sait Aksoy says
Hi,
I followed the direction, but i could not hide the viewer bar. any advise.
thx
Sait
Mynda Treacy says
Hi,
Please provide a link (URL) to the webpage and I’ll check your code.
regards
Mynda
Steve Parton says
Hi Mynda and Phil,
Thanks, this was just what I was looking for. I can get it to work just opening a html file in a browser and also using the html version in a wordpress site. But when I do it using the javascript in WordPress (so I can hide the web app bar) I get nothing showing on the page. I just put all the code in the post area using the text viewer. I also tried using .js files and calling functions as suggested in some forums, but without success (very much a javascript beginner) .
Any tips?
Thanks again,
Steve
Steve Parton says
It all works now. Thanks very much – just had to play around with it long enough. Turning off text formatting fixed it, I think…
Cheers
Steve
Philip Treacy says
Hi Steve,
Glad you figured it out.
The JavaScript will need to be entered into the WordPress post/page using the Text editor. If you use the Visual editor it’ll probably just mess with the code and break it.
Phil
Steve Parton says
Hi Philip,
Again, thanks very much.
Just realised I had hunted all around the globe to find out how to do this, and ended up just up the road.
Nice life up there..
Cheers (from Brizzie)
Steve
Philip Treacy says
LOL, Hi Steve. Yes the internet can be funny like that sometimes.
Glad we could help you out.
Phil
Steve Parton says
Hi Phil,
I promise to stop bothering you very soon..
I had use this approach again but on a different site/account and realised I could not get excel visible without installing the text control plugin and setting it to no formatting/ no character formatting.
I was pasting the code into the text editor at the time and could not see any formatting, but excel did not show until I used the text control plugin.
Is this your understanding or is there another way?
Thanks
Steve
Philip Treacy says
Hi Steve,
No problem, glad to be able to help.
WordPress does a lot of annoying things like try to ‘helpfully’ reformat what you are typing.
I use a plugin called Raw HTML which allows us to use HTML/CSS/JavaScript without WordPress messing with it. It might be that you are having similar issues? Have a look at that plugin and see if it does what you need.
Cheers
Phil
Gabe Milton says
Hi,
Having a bit of difficulty using the html link – the viewer bar continues to have the download Icon despite my using the Javascript in my htm file – is that a limitation of using the htm vs a web page?
Perhaps you can suggest a reference resource as I have additional problems with this procedure – (and realize this a course on dashboard, not ExcelWeb deployment) I seem to be limited to a 700 pxl wide display which is far too narrow (and when I click the full screen button I am given full privileges to open in excel, make a copy etc. Biggest issue is that this approach does not seem to work at all well with a pivot table as the core element – it does not load as an interactive table until it is “expanded” at which point the “Insert Fields” table appears (unwanted) and it becomes accessible for copy/download
Philip Treacy says
Hi Gabe,
Do you have a link to the page you are embedding your workbook in so that I can take a look? If not can you email me the HTML file, you can create a ticket on the Help Desk and attach the file.
With regards to your 700px wide limitation, that’s getting into the realms of web design and CSS but that is one of the hats I wear around here, so if I can help you with that I will.
Phil
Suhaib says
Hey,
I am also facing this 700 px width limitation, is there any way around that I can increase the width, say 1300 px or so??
Thanks and Regards,
Suhaib
Philip Treacy says
Hi,
Towards the top of your code there’ll be a section that looks like this
but in your case it’ll read width: 700px; so change 700 to a larger value.
Cheers
Phil
Gabe Milton says
Hi Mynda,
I’ve been saving this until the db I’m working on was ready to test – but I didn’t realize OneDrive had file size limits (5MB)
My tiny little dashboard weighs in at 58MB and moreover links to the firm’s data cube – Googledrive (eg) doesnt have limits but apparently has Javascript issues (from other posts here) I have an uneasy feeling the file is just too large (its not just the db but the data tables etc) and the server links may also be a problem. I was really hoping to “make this happen” I’d appreciate any ideas you may have for alternatives I could pursue. In any case I’m keeping this trick in my ‘wanna use’ bag.
1.
Mynda Treacy says
Hi Gabe,
Unfortunately that file size does sound like a show stopper. I guess you have two options:
1. Try to reduce the file size. Maybe link to the external data with PivotTables instead of having all the data in the actual file (I’m assuming that’s the reason for the size).
2. Host your dashboard on a SharePoint server. SharePoint has more generous data file size limits.
I expect speed is also a problem given your file size. You could troubleshoot using Charles Williams speed tools: http://fastexcel.wordpress.com/
I hope that helps.
Kind regards,
Mynda
Angel De Jesus Mendivelso Diaz says
Sus aportes son valiosos, me gustaría me ayudes con un archivo de reporte con datos de tres tablas, visitas, trámites, tópico financiero, técnico y tópico administrativo si tienes un modelo que me sirva de ayuda, pues debo esperar el curso en Español para aprender sobre el tema.
Saludos desde Colombia, tema control gestión de empresas de servicios públicos.
Gracias
Mynda Treacy says
Hola Ángel,
Lo siento que no tengo ninguna plantilla que va a hacer lo que quieres.
Saludos cordiales,
Mynda
Sam H says
Hi there,
I was wondering if you would be able to help me with a query around exporting/extracting data from a one drive excel spreadsheet. I have a number of spreadsheets that sit in the cloud (one drive for business in excel). These sheets are updated on a daily basis by a number of different people. What I want to do is export the data out from these spreadsheets into a central repository (probably a db, or maybe another spreadsheet) – and then from there I want to build a dashboard.
What I need help with is extracting the data. MS support are telling me that it is not possible to extract/export data from a one drive spreadsheet – but I find this very hard to believe. Do you have any ideas about how to do this?
In google sheets, there is just a formula that is able to do this. But I am having a great deal of difficulty doing this in one drive.
Thanks!
Mynda Treacy says
Hi Sam,
I just tested this with some data formatted in an Excel table in a file on my OneDrive account and it worked.
In your Excel workbook:
1. Go to the Data tab of the ribbon > Existing Connections > click on the ‘Browse for more’ button at the bottom >
2. In the folder list (the left hand pane) browse to your OneDrive folder >
3. Change the data source to ‘All files (*.*)’ and select the Excel workbook you want to extract the data from >
4. This will give you a list of worksheets and tables in the file and you can select the one you want and click ok (for best results I suspect it will be ideal if the data you want to import is formatted as an Excel Table) >
5. In the next window choose whether you want to bring in all the data in a Table, PivotTable report etc.
Once you’ve connected to your file on OneDrive you can refresh the connection using the Refresh button on the Data tab.
I haven’t tried this with Access but I expect it will work in a similar way.
I hope that helps.
Mynda
Sam Holdem says
Hi Mynda,
Great! Thanks for your help, I really appreciate it.
Unfortunately it looks like online sheet to online sheet all updating in real time is not possible in excel at this time. However I now have a fairly good workaround – every time I need the data repository sheet updated from the individual cloud based sheets I just need to sync the files in onedrive online to my local disk, then open the repository file on the local disk and hit refresh data – it then pulls all the data in from the 3 data entry sheets to the one place.
Thank you so much!
Mynda Treacy says
Oh, good. Glad you found a workaround.
Mynda
Sam Holdem says
Hi Mynda,
Just wondering, do you offer a course on creating dashboards using Power BI for Office 365?
Thanks!
Mynda Treacy says
Hi Sam,
Not currently, sorry. I’m in the process of creating a Power Pivot course and will be including Power View and Power Maps in that. It’s a few months off yet though.
Kind regards,
Mynda
Ivan says
Mynda,
thank you very much for sharing this information! It saved me a lot of time and i was able to give to our users a great way to show their charts in a dashboard page!
Thanks thanks thanks 🙂
Ivan
Mynda Treacy says
Brilliant, Ivan 🙂 glad to be able to help.
Mynda
Rojas says
This is amazing stuff. You have covered things I never thought were possible. I will be trying this out. I am playing catch up hence the late post, but I thought it was worth a mention anyway.
Mynda Treacy says
Awesome, thanks Rojas 🙂
Adi says
Superb stuff Phil & Mynda,
Thanks for a lovely tutorial… I just keep growing more and more indebted to MyOnlineTrainingHub.
Cheers,
Adi
Mynda Treacy says
Cheers, Adi 🙂 Glad you liked it.
Mynda.
Henk says
Hello Mynda.
Great post, so i tried it. But when i uploaded an Excel-workbook to my OneDrive and i selected Embed, i did not get the link you said but only a html-code-link.
Did i do something wrong? Doesn’t it work in the Dutch OneDrive?
Regards, Henk.
Mynda Treacy says
Hi Henk,
I don’t know if there are limitations to the Dutch version of OneDrive but I doubt it.
Under the ‘Upload it to OneDrive’ instructions did you click the link in Step 4 to customize the appearance, and then in Step 5 click on the JavaScript tab?
In step 4 you will also see some HTML code above the link to customize the appearance so I wonder if that’s as far as you got?
Kind regards,
Mynda.
Henk says
Hello Mynda,
There is nog link in my OneDrive at step 4. When i click on “Embed” there appears a new page with a text like “file.xls embed in blog or webpage”. Below this titel there’s some text and a html-code.
I’ve tried to change my account in Outlook to an other country but it didn’t change anything for me.
I have a free Outlook-account, no Office-365, only Excel 2003 and 2010. Does that make any difference?
Regards, Henk.
Mynda Treacy says
Hi Henk,
I don’t think it has anything to do with the fact that you have a free account. Mine is also free.
I’m not sure if Excel 2003 has any restrictions, but it should work with Excel 2010.
Perhaps it is a region specific restriction but I’m not aware of any. If you’re able to email me a screen shot via the help desk of what your screen looks like at Step 4 I can look into it further.
Kind regards,
Mynda.
Henk says
Hello Mynda,
Today I found the the reason why my Excel-2003-file didn’t work.
I did not start in OneDrive with a rightmouseclick on the file but i opened it. Microsoft told me that i used a scrollbar-form which wasn’t supported in the webbased Excel and when i opened the file it wasn’t there anymore.
I saved this file (without the scrollbar-form) and i tried “Embed” again. Now i got the same screen as you wrote and i can see the Javascript.
So, my problem is solved and i guess i will use it a lot.
Thanks for this blog and for your replies.
Regards, Henk.
Mynda Treacy says
Hi Henk,
Great to know you cracked it 🙂
Mynda.
Peter Urbani says
Hi Mynda
Great tutorial thank you very much. One question, is there a way to automate so the user doesn’t have to hit enter after making a new selection from a dropdown list i.e. the equivalent of an on-change or on-event trigger in VBA ?
Kind regards and thanks again
Peter U
Mynda Treacy says
Hi Peter,
Glad you liked it 🙂
No, you can’t automate the password entry. Unfortunately you can’t execute VBA in the Web App. Perhaps you could put a dummy button (Excel Shape) in the worksheet that says ‘Enter Password’ to get them to deselect the password cell, which would have the same effect as pressing ENTER.
Kind regards,
Mynda.
Vin says
Hi Mynda!
Very interesting post – You have demonstrated the solution for the real problem, Really Appreciate it.
Could you pls let me know how do I do this :
1. can I do this without uploading excel file into onedrive?, If yes then 2nd Question –
2. would like to know how. How can I show the same in the intranet web page without using Onedrive – then where do I upload the file.
Mynda Treacy says
Hi Vin,
Glad you think the Excel Web App will be of use. Unfortunately you must upload it to OneDrive in order to embed the file in a web page.
It doesn’t mean you can’t use the file anymore, it just means instead of opening it from a folder on your PC or the server, you browse to your OneDrive folder and open it from there. Everything is the same once you open it, it’s simply the location of the file that is different.
Kind regards,
Mynda.
Vin says
Thanks Mynda – But unfortunately, I don’t have internet access in our company, so I have only intranet option.
Now just am thinking how do I make use of this technique. where do I load this file.. can I get the embedded link option if I load the file in server – intranet..? Can you pls suggest.
Vin says
Addition to that, in few people has internet access, but no access for onedrive.(as it’s confidential data, we can’t load into internet, we are allowed to use intranet) Pls suggest.
Mynda Treacy says
Hi Vin,
Sorry I don’t know of any other way. You might like to search Google (from one of the computers that does have internet access 🙂 ) and see if you can find any 3rd party software that will do it.
Kind regards,
Mynda.
Maxime Manuel says
Mynda and Phil… I carefully read and enjoyed this post. I really do not know what to say. Damn it!
I was looking to do this using Google Spreadsheet. I had to use a form for the user to fill using a dropdown menu. This for is connected to Google Spreadsheet. I thought that Excel itself could not do it. I was wrong. Excel does it perfectly. OneDrive embeds better than Google Drive.
I did not fully understand how to perfectly use the JavaScript. But I will give it a try during the weekend and hide that bottom bar.
Many thanks to you and Phil. Like I use to say, you must come from another planet. Lol
Mynda Treacy says
Cheers, Maxime 🙂
Keeping it all Excel makes things a lot easier. I think Excel Online is gaining traction on Google Spreadsheet.
You don’t have to hide the bottom bar, I just like to for presentation purposes. As long as you embed your workbook using the JavaScript you’ll be fine.
Mynda.
Maxime Manuel says
I just tried the JavaScript part and it just worked. Thank you again!
Philip Treacy says
Great! Good job 🙂
Maxime Manuel says
I have just embedded a file from OneDrive that is working well. The only problem is the language. From Excel 2013 and Excel Online, my Excel menus and Formula are English but from the embedded Web App menus are in English and formula are in French. There is a problem every time a user enter an US date (mm/dd/yy). Only European date work with it (dd/mm/yy).
Did you face such issue and how did you do? Please share with me 🙂
Mynda Treacy says
Hi Maxime,
I believe that Excel Online and the Web App adapt to regional settings you have on your PC and online account. You can read David M from Microsoft’s explanation below:
“Your regional settings are derived from the “Account Settings” section of your Microsoft Account. From SkyDrive, you can click your name in the top right, then click Account Settings. Clicking “Edit Personal Info” takes you to a page that lists your Country/Region.
If you want different regional settings today, you must change this Country/Region setting for your Microsoft account.
For most people in most scenarios, this setting does provide the locale information they expect based on their country or region in which they live. However, there are scenarios such as these where more granular control is desired. In this case all data is rendered according to your regional settings, and UI/menus are rendered according to your language settings. Formulas happen to be part of the data, and so they are rendering according to your regional settings.”
I also found this info about the date settings which might help:
http://office-watch.com/t/n.aspx?a=2070
I hope that helps.
Mynda
Philip Treacy says
Hi Maxime,
If you have problems with the JavaScript let me know and I’ll see if I can help.
Phil
Maxime Manuel says
Phil, I appreciate your help a lot. The only thing is that Google Sites does not some parts of the JavaScript. I guess that there should be nothing to do about it. I had to embed it to Google Sites and it accepted it. Maybe I am doing it wrong by copy/paste a JavaScript into Google Sites HTML.
At the end of the day I did it with the embedded code. You guys are geniuses. Sometimes I wonder if you are human. Trust me! (Laughs)
I will try to push my work further. Your assistance is precious. Many thanks Phil.
Philip Treacy says
Hi Maxime,
I’m not familiar with Google Sites so not sure how/if they allow you to modify the Javascript.
But if you got it working with the iframe embed code then good job.
Glad we can help 🙂
Cheers
Phil
Pavel says
Hello,
I have problem with embedded JavaScript, but have no problem with iframe.
I don’t know why, It doesn’t show on my page if I use JavaScript.
Interesting moment: some time ago it was working, but at one time stopped (I didn’t change it).
So,now I can’t hide Excel bar at bottom, because I use iframe now.
I thought maybe it can help me if I’ll use CloudFare as a proxy. But it also didn’t help me.
Can u help me with it?
Philip Treacy says
Hi Pavel,
We’ve recently encountered the same problem and we’ve raised this issue directly with Microsoft, they are investigating it right now.
My own investigations show that there is some issue with the JavaScript being supplied by one of the Microsoft servers. It appears to only affect certain regions though, as people we know in the USA haven’t reported any issues.
Where are you when you load these pages and get the JS errors? I can tell the Microsoft engineers and it may help them diagnose the issue.
Regards
Phil
Pavel says
Thanks for reply.
I’m in Vietnam now. But my friend from Russia also can’t open my web site.
That’s why I tried to use CloudFare as proxy.
But it didn’t help me.
I tried to change Microsoft profile’s settings, and it did something.
I don’t understand clearly what it exactly does).
But diagram was changed (maybe data formats, or comma’s type… I don’t really know).
I also tried to use different foreign proxy servers (USA, Denmark, Australia) and it was same effect.
So, maybe problem on MS side. Maybe filter by regions or something else.
Thanks.
Philip Treacy says
Hi Pavel,
OK that makes sense unfortunately! My testing shows that the problem is affecting those in Asia, including Australia and New Zealand.
To me it looks like there are a number of Microsoft servers that serve up the JavaScript for the web apps. Which server you get is determined by where you are. With you in Vietnam, and us in Australia, we are both receiving data from the Singapore server and this is the one that isn’t working properly.
When you connected to proxies in other countries, your browser may not have actually loaded an error free copy of the Microsoft JavaScript for loading the Excel web app. The browser may have just used a cached (saved) copy instead, which didn’t work.
I’m afraid all I can do is wait until Microsoft figure it out and fix it. We raised the problem with them last week so I expect some action on it this week.
Regards
Phil
Diana Criscione says
Interesting way to handle this! I don’t think I could output what I’m working with to the cloud, but you never know when you’ll need to do something like this.
I’ve got the same issue as mentioned in your newsletter in that every month I email 30+ spreadsheets which have information specific to the individual receiving the workbook. I use an Excel template along with some VBA code (in Access) which loops through my list of people and does the following: open the template, dump the data into it, save the file, create an email, attach the file, and then finally send the email. It doesn’t take long for the process to occur once I have the data downloaded from our main system. With a couple of clicks, off they go!
Mynda Treacy says
Hi Diana,
The cloud (OneDrive) itself is secure, it’s only an issue if you publish your web page that contains the Excel Web App on a page that anyone can access. If it’s on an intranet, or you just create your own HTML page (as in the instructions above) then it’s no less secure than emailing a password protected Excel workbook. In fact I’d say it’s more secure than a password protected Excel workbook.
Fortunately for you, you have VBA skills but most people don’t, so the web app is a nice alternative. Plus if you need to re-create/update the Excel file you don’t have to email everyone with the file again. The changes appear in the Excel Web App automatically (well….if the page is already open then you need to refresh it).
Cheers,
Mynda.
Bryan says
This is super cool! I think the Web App is my favorite “feature” to come out in the last couple of years. So far I’ve only used it for planning parties or social events, but maybe someday someone will let me have some real data to play with :-/
Mynda Treacy says
Hi Bryan,
I think the Excel Web App and Excel Survey are underused right now, so it’s nice to know you are using the Web App, even if it’s for fun stuff 🙂
Mynda.
Lance Smith says
Hi Mynda
This is an awesome post. I learned so much since joining your excel course.
Mynda Treacy says
Hi Lance,
It’s rewarding to know you have found the course helpful. I’m really liking what we can do with the Excel Web App. It’s only getting better as Microsoft further develop it.
Cheers,
Mynda.
Jef says
Cool article. I am sure many users will find this very helpful and save them a lot of time. Thanks for sharing.
Mynda Treacy says
Cheers, Jef 🙂
CMA Vishal Srivastava says
Hi Mynda,
What an Excellent Post. I admire you most!
Mynda Treacy says
Thanks! 🙂
Stephan, England, Hull says
Hi excellent, Embed includes DVCombo Boxes, that’s news to me! Presumed feature only in EditWorkBook to prompt Combo Boxes to reappear!
My simple way: ONE DRIVE, UPLOAD, r/c EMBED, EDIT WORKBOOK, EXCEL ONLINE, FILE, SHARE, EMBED, HIDE GRIDLINES/HEADERS, EXC DOWNLOAD LINK, INC FILTERS, INC START CELL. COPY EMBED CODE.
You might say why not just OneDrive Upload then Embed? Because this way, there are more OPTIONs on Embed!
see my online Spreadsheet Ex: Framed Pages & Non Framed Pages:
select from 4 Combo Boxes highlighted in Yellow on r/h,
watch the main body values changes as you make your selections!
http://www.hkrebs63.karoo.net/xl/
Also my spreadsheet is all homegrown edit Formulas of countif / index / match, so no hidden VB Macro!
Mynda Treacy says
Hi Stephan,
Thanks for sharing your example.
I think you may have misunderstood a few things in our example. e.g.
– It doesn’t contain Combo Boxes, just Data Validation lists.
– We also right-click and embed (step 3) and then choose from the options (step 5).
– We only use VBA to set the hidden status of the sheets. It doesn’t excecute code, partly because you can’t anyway in the WebApp, but also because there is no code in the workbook.
– Everything is generated by formulas.
The overall functionality we were trying to provide, which the WebApp doesn’t do easily, is to prevent people downloading your workbook and getting their hands on your data and “homegrown” formulas and IP.
Cheers,
Mynda
Stephan, England, Hull says
Hi, Excel Online Embed is a great function, yours is a great looking & functioning example. MSN should have done Embed years ago!
The OneDrive Embed feature makes Source Directory Folder less obvious then conventional FTP host, without compromising WRITE to source ftp permissions like ftp.
My Example Data isn’t privileged, so can see formulas, my method was Data Validation list Combo Boxes, Defined Names and Index Match Countif. I think DV Combo Boxes look neater then Macro buttons, and on Embed guess some Macros would be unsupported features. As a training professional I understand your work is privileged to yourself to demonstrate to users via training.
Embed Option to Share/Filter/Type option enables Data Validation Combo Boxes, without edit/save/download to source option from external user to User Account. That was my point. Do you know of a Picture retrieve Formula that works with Embed?
Example Excel Embed inc Graphs & 1ComboBox: CAR BHP with Statistics & Graphs,
it does looks a bit planer then newer Excel versions, this is Excel 2003 file, see Combo Box J1
http://www.hkrebs63.karoo.net/xl
Mynda Treacy says
Hi Stephan,
I have a hard time following what you’re trying to say so I apologise if I misunderstand any of your points.
To be clear; you can’t use Macro’s (at all) or macro buttons in the WebApp and “data validation combob boxes” don’t exist. It’s either Data Validation OR a Combo Box. Combo boxes are form controls and do not work in the WebApp.
I personally was not trying to protect my IP, but rather teaching a solution to others who do want to protect their data or IP.
Unfortunatley images and Shapes are not supported in the WebApp.
Mynda
Stephan, England, Hull says
Update with Graphs, see J1 ComboBox:
http://www.hkrebs63.karoo.net/xl/graph
Picture retrieve in Embed?