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 fairly 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 a OneDrive Personal account (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 fairly 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 accidentally 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 require a different approach.
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, TIP Right click this link and choose 'Save As' to save the file to your computer.
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.
Hiding the Viewer Bar
Because Microsoft sometimes change the code that controls the way workbooks are embedded, I find that the JavaScript to hide the viewer bar sometimes does not work until it is updated to account for Microsoft's changes.
Should you have problems, contact me and I'll fix things up.
Hiding the Viewer Bar With JavaScript
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.
I hope you can help… How do you get rid of the scroll bars on the right, and bottom, of the embedded worksheet. I don’t recall having this issue in the past, or, maybe I never paid attention. I tried all sorts of Google searches with no luck. Is there something that can be entered in the HTML code to eliminate, or hide, these scrollbars. I see them at the embedded dashboard at the beginning of this article, for example. Is there something similar that can be done as you did to hide the black Excel bar at the bottom.
Any help would be much appreciated.
Hi Mike,
Please see this updated tutorial on embedding Excel in web pages.
Mynda
Thanks very much for your reply. I also saw this article/blog, but I did not see any mention of hiding the thin/white scroll bars to the right and bottom of the embedded Excel worksheet. Note, even the pictures in this article/blog have the scrollbars shown. Changing the px size just moves it more to the right, or down further.
Is there any way to disable or hide those scroll bars, as one can hide the grey/black Excel bar at the bottom (your other article’s added code still work for that – thanks). Any thoughts would be much appreciated!
My bad, Mike. AFAIK there’s no way to hide the scroll bars, as annoying as they are. You could try deleting any columns/rows outside of your dashboard area to reduce the scrolling range i.e. don’t have any data in cells outside of your dashboard area as that’s what determines the amount of scrolling available, even though it doesn’t necessarily show the data. I don’t think you’ll get better than that.
Yes, well said, they are annoying!
However, I now see on your Interactive Dashboard at the top of this article, that there are NO visible scroll bars, just a very faint vertical line to the right of the dashboard, no scrollbars! I don’t know what changed, but they are no longer visible! I am not going insane, they were there less than 24 hours ago (I should have taken a screenshot). I re-tried my code, and there were no scrollbars, just that same faint vertical line to the right of the embedded worksheet as in yours. If I play with my width ‘px’ settings, it is barely noticeable. I do not know what changed, but there are NO scroll bars!
I cannot thank you enough for this article and your support… thanks again…
FWIW… During the past day, when those nasty scroll bars could be seen… I did play with your idea in “The Solution to Incorrectly Displayed Workbooks”,and I was able to reference my htm file… and by carefully changing the width and height settings, I was able to hide the scroll bars (I think).
Every time I google this issue, your article remains the only solution.
Again, thanks very much… genius…
Hi Mike,
Glad they have miraculously disappeared! I suspect Microsoft are changing things in the back end. We’ve had this many times over the years. Don’t be surprised if they reappear.
Mynda
Hello I need assistance on this setup , can any one help me please
Hi Rao, We don’t do consulting, but if you reach out via email: website @MyOnlineTrainingHub.com, we can put you in touch with someone who can help.
Hi, thank you for this, it is the closest thing to what I have been searching for. Is it possible to remove that bar if the excel file is embeded rather than using a link? If so, I would really appreciate the code to this. I managed to do it years ago, but I think Microsoft changed something.
Thanks in advance.
Glad we can help, Shayne. The instructions above are for embedding, so I’m not sure what you mean by your question.
Hello. Thank you for the info. I have recently tried to hide the lower webapp viewer bar with no luck. Can you help?
Hi Matthew, it’s working for us, so I suspect there is something amiss in the steps you’re taking. Please review them as this only works with OneDrive personal accounts. If you’re still stuck, please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum
Hi mam, Your video content and explanation are very good and easily understandable. very happy to have such a great and wonderful Tutor. Below is my Query: I am unable to create web app from the text file. I have done it till getting java code. From your notes, “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\”. How to do this. Could you please share exampler one to make my Job easier.
Hi Sudhakar,
When you say you can’t create a web app from the text file, it’s not clear of you are having problems with the HTML file or if the problems is getting that file onto a web site.
Can you please start a topic on our forum and attach the HTML file and I’ll take a look at that.
Regards
Phil
Thank you for the Quick reply. I dont know how to use my Java script (html file’s data) to make it as a web app. I could generate Java script as per the above technique, but dont know how to proceed further
Hi Sudhakar,
If you have created the html file, just open it like you open any file with a double click, it will open in browser.
Mynda,
22 yrs IT and very Novice programmer. I’m now a Builder for 15 years. I’ve used Co construct, WorkIZ, Estimate Rocket, and BuilderTrend. None really hit the mark and I’m debating developing something on my own. I was doing basic research and came across your articles (videos). They are AWESOME! I had not considered excel or BI but your video peaked my interest. Do you think an excel or BI dashboard could be made to replace the above listed project management softwares for builders? I’d love to discuss this in more detail through email if you have time. I understand if not.
Feel free to remove this post from your blog as I know its not on mark.
Thank you for your time and your VERY informative videos!
Ray M.
Hi Ray,
Most probably it can be built in excel more easily and flexible than power BI, but will be a significant effort, I assume those software apps you mentioned are not simple to replicate and build more functionalities on it.
Anything is possible.
Regards,
Catalin
Thanks for all your work on this topic, and for everyone’s comments – I learned a lot.
One bothersome quirk I was hoping you could help me with… when I right click almost anywhere on the embedded file online, it gives me the option to copy (which is not a problem, although there is no ‘paste’ option), but also gives the user the option to Sort (for whatever reason?). When setting up the embed process, it does not seem to make a difference whether one checks, or uncheck, in the ‘Interaction’ section, the ‘Let people sort and filter’. In your process, for example, it appears you did not check that box, yet, the ‘Copy/Sort’ right click option still presents itself on your embedded file. Is there any way to turn OFF this feature and have a right click on the online file do nothing, or at a minimum, just let the user click ‘Copy’, since that is harmless without a paste option? For example, if the user highlights column G in your ‘Dashboard’ example, and sorts it, it all goes nuts.
Thanks
Hi Mike,
Have you actually tried to perform the sort, or just noticed it as an option in the right-click menu? Because when I select sort e.g. the year column header labels, nothing actually happens.
Mynda
Thanks for your response… to answer your question(s)… yes…
– Here’s your html link to make sure we are looking at the same webpage: https://d13ot9o61jdzpp.cloudfront.net/files/moth_embedded_workbook_js_2004.html
Yes, I selected all of what would be column G, or specifically G1:G31, or either of the the “Trend” columns actually. Highlight all of column G, top to bottom, (it will even tell you you are sorting column G if you select ‘Custom Sort’), right-click, Sort anyway you want… and the data, graphs will go missing, the word “Trend” will move to the top rows, etc. I assume this could be fixed by protecting cells prior to embedding, as your other columns won’t allow one to sort (an error message pops up, as you mentioned). However, for anyone setting up an embedded sheet where the user is to make entries, one ‘Sort’ of this nature would mess things up, so being able to turn that feature off as it appears Microsoft has it set up, would be great!!! But I have tried that option checked, unchecked, “Embed” or “Java”, no matter which way, the pesky/unwanted “Sort” option is still there… so assuming you get the same results as I when sorting the column I mentioned (not that it makes to do so, but some users will play around if a feature is available, especially “Copy”, when no ‘Paste’ exists), is there any way that you know to turn this feature off?
Thanks so much for looking at this.
Hi Mike, I don’t get this behaviour in my browser. I can choose a custom sort on column G, but nothing actually gets sorted and I don’t lose the sparklines. I’m not aware of any way to prevent the right-click options from appearing. BTW, CTRL+C and CTRL+V will copy and paste the data from the web app to Excel. Mynda
Thank you very much for investigating…
Everything works just wonderfully (hiding the excel bar at the bottom) except the vertical scroll bar to the right of my excel data. That’s how your example (moth_embedded_workbook_js_2004.html) appears to me as well. Regardless of how much I change the height of my javascript, it doesn’t eliminate the vertical scroll bar. Is this possible to eliminate while using the javascript method? Also how do you align it on the page centered for example rather than left aligned?
Hi Steven,
It would appear that Microsoft have changed something with the code that controls the embedding. They do this from time to time. I can’t figure out a way around it either I’m afraid. Hopefully they will switch it back to the way it was asap.
As for centering the embedded workbook. You need to add auto margin to the DIV like so:
Regards
Phil
Hello,
I have tried to hide the viewer bar with your JavaScript but it doesn’t work.
Could you please provide me a new one… I am working with Excel 2019 and I have exported my file on OneDrive.live.com ?
Thanks in advance
Hi Christine,
I’ll need your file(s) so I can make the necessary changes. Please start a topic on the forum and attach your file(s).
Regards
Phil
Hi Mynda,
Thanks for this post. I have an Excel dashboard that I need to deploy quickly but am concerned about the security issues you raised. My thinking is to take your Power BI course, but first I have two questions:
1) I assume that the dashboard I build on Power BI will be completely secure (assuming I employ password protection, set it up properly, etc). Before purchasing your training course, I just wanted to confirm that my understanding is correct?
2) As far as using an Excel dashboard in the interim, using your example above what if the dashboard was separated into 4 workbooks stored on OneDrive. The user would have access to the dashboard workbook which, in turn, would link to separate workbooks containing data for Bob, John, and Richard. If someone were able to hack and download the dashboard workbook, they would see only links to the other workbooks (not the data itself). Would this make the data secure in the interim while I build the Power BI dashboard?
Thanks for any insight you can provide!
Hi Alan,
Yes, if you share your Power BI reports with other people with a Power BI Pro licence they will be required to login to the Power BI Service to view it. No login = no viewing. This also means they will need the US$10/mth Pro licence.
If your dashboard is built using PivotTables and you want to retain interactivity with Slicers, then even if the source data is in an external file, people can still access the underlying detail because it’s stored in the Pivot Cache. A simply double click on the Grand Total cell will give them all the underlying data for that PivotTable.
You can choose to not ‘save source data with file’ in the PivotTable settings, and this empties the cache on saving of the file, but then you cannot use the Slicers until the connection is restored and the Pivot Cache contains data again.
Of course if you don’t need any interactivity, then you can empty the cache upon saving and users won’t be able to access the data if they don’t have access to the location the source file is stored. If they have access to the file location, they can simply ‘restore connections’ and the Pivot cache will be populated again.
If you use formulas instead, then you’re likely to have problems because many functions return errors unless the source file is also open.
I hope that clarifies things.
Mynda
Yes, thanks very much for the thorough reply. Looks like I’m on my way to Power BI training!
I’m sure you’ll love Power BI 🙂
Hello,
Thanks for this guide, very helpful so far.
I have an excel file with a couple of very basic VBAs that i’m trying to get working in Java script.
With your guide I have been successful in making a dashboard display appear on web
However I have a few queries.
1) I notice that my file loose any VBA functionality it had when I convert it.
Basically on my main dashboard there is text displayed a main display box which updates when I hit a proceed button, which is just a simple VBA that add +1 on to a hidden cell, that is then v lookuping the text to display.
2) I seem to also loose the ability to jump between sheets, Ideally i need the user to be able to jump from the dash board from one other sheet where they are able to select a list of actions from drop downs and back and forth If i add the entire workbook this works but it then displays the whole excel sheet but i want it just to display the same size of view as the dashboard in both.
3) Finally my file also has a second simple VBA which copies a line of numbers, which are the results of their drop down selections and pastes it into another
sheet on the first empty row. Then when the count in first tab reaches 30 it then deletes all these row.
This is an excel text based game i was working on which is run over 30 weeks, the count in number 1 relates to week 1, for each week you must make selections from drop downs in 2) before hitting the update button on the main tab, the results of drop downs for each week are pasted in another tab to allow the calculation of a running total on the main dashboard and then when the game ends at week 30 the game is then reset, e.g counter back to 1 and delete all the pasted data.
Not sure how possible any of this is but your advice would be appreciated.
Thanks
Kevin
Hi Kevin,
Unfortunately, you cannot execute VBA in Excel for the web, including Excel Web App, SharePoint or Excel Online. The web alternative is to use the Excel JavaScript API, however it is still being developed and you won’t find the equivalent functionality for everything we currently have with VBA.
Mynda
Hi Mynda,
Thank you for another great lesson. It’s really valuable. I have a scenario like below, hopefully you could help me answer my questions:
– I have a excel workbook called WB 1 to store Mr data source
– I have another excel workbook called WB 2 which has my dashboard.
– WB2 reads data from WB1 to run all formulas and pivots charts and tables.
– Both WB1 and WB2 are stored in a shared network drive
– If I upload WB2 (where my dashboard is) to OneDrive ONLINE (only) to publish it on our intranet. Will my WB2 be updated automatically every time my data in WB1 is updated?
NOTE:
– I can NOT store both workbooks 1 and 2 in personal OneDrive desktop app, due to restrictions in my organisation. They only allow OneDrive for web.
– I can NOT put both my data and dashboard in the same workbook because the data is over 5MB and changes everyday. Therefore, I have to set my dashboard in a separate worksheet. Otherwise excel online can’t open my workbook for view online on a web browser.
So, my BIG question is, with the whole situation like this, will this web embedded solution work for my case?
THANK YOU VERY MUCH for your time to share your knowledge with the community and also to support me with my questions.
(I’m a big fan of your YouTube channel. I watch them everyday, and honestly, your great tutorials have helped me strengthen my excel skills. THANK YOU!)
Hi Adrianna,
You will need to open the WB2 file on OneDrive in the desktop Excel app to refresh the links to WB1, then save, for the changes to be visible in the dashboard in the Web App.
Mynda
How to save data which is inserted in embedded excel to database.
OR how to generate PDF file of inserted data in embedded excel
You can ‘Print’ to PDF to create a PDF version of the report.
Hi Ravi,
You can set the cells to ‘write’ access when you create the embed code.
Mynda
Hi Mynda,
I’m using your solution now for quite some time and I think it’s great!
Especailly the part where you can hide the Webapp lower bar, preventing users from opening the file online.
However, I’m running now into an issue where I created a colomn where the user is able to type in data.
Now the user wants to copy and paste multiple fields from a column in another excel file into this column, but that doesn’t work.
It seems only possible to paste one value at once.
Is there a possibility to paste more values at one time in the webapp (JAVA)?
Best regards,
Nico
Hi Nico,
I’m not sure. I haven’t played with the JavaScript much, sorry.
Mynda
is there anyway to publish Excel Dash board file more than 5 MB in One drive?
Hi Shiju,
Maybe with SharePoint.
Mynda
hey bro it’s not working I don’t know maybe a lack of knowledge of HTML and similar just because of I’m in mechanical field or there is something else reason.
I go through every step you mentioned. this works fine. but from the step to enter in the webpage it doesn’t work.
I embedded code in site but it doesn’t let the viewer to enter the value in the excel cells.
kindly help me regard this.
Did you check the box to ‘let people type into cells’ after you upload it to OneDrive (step 5)?
Great article – thanks for sharing your knowledge with us!
I have set my Excel file up in the Office 365 version and saved it to my company’s Sharepoint drive. I can access it on my website when I am logged in to Office 365, but when someone else not logged in tries to access the webpage with the workbook, they get a message from Sharepoint Online requesting a sign-in. I need anyone to be able to access it without logging in. Is there a way to do that?
Hi Nick,
This sounds like a SharePoint restriction, so I’m not sure. You’d have to speak to your IT people. Maybe you can share it on an intranet page without any restrictions.
Mynda
hi
i am data Analyst , your article very helpful . i want to ask an question.
1. drop down list is not working when i save and create a web page and work .
2 . how can an other person see the dashboard on any time .. how can be it live … i had sent him a link file:///C:/Users/Shabbar%20Ali/Desktop/SHABBAR%20TESTING.html
Hi Shabbar,
Please post your question, HTML file and Excel file on our Excel forum so we can troubleshoot. Unfortunately, we can’t tell anything without seeing them.
Thanks,
Mynda
This is awesome but I cannot find the JavaScript embed anywhere (only option is iFrame). Is that option only available for certain subscriptions? We have O365 Business Premium.
Hi Eric,
At the moment the JavaScript option is only available with OneDrive free accounts.
Mynda
Correct me if I am wrong, but I believe a noticeable difference between OneDrive for Home (or Free), and OneDrive for Business… is the Business edition allows you to specify that the file can NOT be downloaded (although the edit function may be unavailable?). Since you mention in your ‘Caveat’ section that a knowledgeable person could eventually get to your OneDrive (Home/Free) file, and somehow download it… do you know if this would be possible in OneDrive for Business, for an intruder to get to one’s file, but be blocked when trying to download it???
Everything about this embedding feature is so cool, but the security issue as you and others have mentioned, likely remains a concern to many of us.
If you have any knowledge of the OneDrive Business download block feature as it relates to all these posts – could you please briefly share? Maybe a small price to pay for security?
Thanks so much for taking the time to look and respond to these posts – it’s very kind and professional…
Hi Mike, both OneDrive Personal and OneDrive for Business have an option to ‘include a download link’ but this check box is useless to prevent people downloading the file because the viewer bar has an option to view in full screen and from there you can download the file. There’s no way to block someone from downloading the file from OneDrive for Business, and in fact, with this option you can’t get the JavaScript embed code that allows you to hide the viewer bar. This is all explained in the post above.a
Thank you for the great article! I was able to publish the dashboard I wanted and it looks fine in the web browser, but I get an error message saying “This workbook contains external data connections or BI features that are not supported.” when I try to interact with the slicers on the report. I am using Office365 Home. The dashboard is pulling from the spreadsheet’s data model, but all of the data is housed in tables in the workbook (no outside data sources). Any suggestions?
Hi Jason,
At this point in time the WebApp doesn’t support the Data Model. Can you create your PivotTables as regular PivotTables instead of Data Model PivotTables?
Mynda
Hello,
First of all, thanks for this information. My question is about Excel APIs.
You added following code in your example link “moth_embedded_workbook_js.html”. Also onedrive gives the following code for my excel.
script type=”text/javascript” src=”https://onedrive.live.com/embed?resid=8EBE2BB908DA7CDE%211011&authkey=%21AOVyxlcx_wVZJgo&em=3&wdItem=%22dashboard%22&wdDivId=%22myExcelDiv%22&wdHideGridlines=1&wdActiveCell=%22’Dashboard’!C3%22&wdAllowInteractivity=0&wdAllowTyping=1″ /script
But “Excel Apis” described in the link “https://msdn.microsoft.com/en-US/library/hh315812.aspx”. And you added folowing code at the top of the page for example which is Interactive Dashboard with Password Security Hosted on OneDrive.
script type=”text/javascript”
var fileToken = “SD8EBE2BB908DA7CDE!1011/-8161037401031279394/t=0&s=0&v=!AOVyxlcx_wVZJgo”; //MOTH
How can add this filetoken code?
Thank you.
Hi Omer,
Using the fileToken is an old way of embedding a workbook. OneDrive currently does not use this and neither does my sample HTML file.
Microsoft have not updated their documentation. I will mention this to them.
Thanks
Phil
Hello, Is there any alternate method to publish interactive excel worksheet on web such that people can access and edit the dashboard. I am using One drive for business. I have sharepoint, is there a way where I can publish it on sharepoint as interactive web.
Hi Hardik,
Yes, you can use SharePoint. The process depends a bit on the version of SharePoint Server that you have installed. This explains it for SharePoint Server 2013. If you’re using a different version of SharePoint then a Google search should reveal the instructions you require.
Mynda
Hi Mynda,
Great idea! thanks for the sharing.
I have 1 question, is there any files size limit applicable?
Hi Ritesh,
Yes, there is a 10MB file size limit.
Mynda
Hello,
I have just find this useful site, have read the whole article but could not undertsand what is the connectiion between “user” and dashbord i.e. how it should be connected in order data to be presented correctly (on previous videos we have seen only slider, but not the dropping option to be connected with sliders).
Please provide some info
Hi Robert,
I’m not sure what you mean by ‘slider’ and ‘dropping option’. I suspect you mean Slicer and Data Validation. If so, the links take you to tutorials on those topics.
Mynda
Hi Mynda,
Sorry that i was not so precise, my meaning was how the excel is doing the connection between the “Choose user” and “Enter Password” and “Slicer”.
I have read and understand the validation of the User and Password, but what after that, what is needed to be done in order to have proper filtering of “Slicer” defined by the log in data (“Choose user” and ” Enter Password”, i’m missing that part).
Thanks in advance,
Robert
Hi Robert,
In Step 1 I explain how the password is validated using a formula. I can’t really explain it any differently. Why don’t you try replicating what I explain in step 1 and if you get stuck, upload your file and question to our Excel forum where we can help you further.
Mynda
Hi Mynda,
I have gone once again through the manual and have understand the security checking (“username” and “password”).
Unfortunately, could not understand the connection between username and changing the data in the charts etc. how they are related (changing of username changed the charts data)? any advice for topic that can be useful to repeat it.
Thanks in advance for support.
BR
Robert
Hi Robert,
In the background I have PivotTables that contain the data for all salespeople. I then use the GETPIVOTDATA function to extract the data from a PivotTable for the selected salesperson and populate a separate table that feeds the charts.
It’s a bit tricky to explain here, but I cover it all in detail in my Excel Dashboard course.
Mynda
Mynda
Mynda,
My dashboard data comes from a SSAS connection. Is it still possible to use the Web App to distribute it?
Thanks.
Hi Joseph,
Yes, but you can’t refresh the data from the Web App itself. You’ll have to open the file in the desktop version of Excel to refresh/update.
Mynda
Thanks.
Editing Cells
Hi I have a protected sheet embedded on my blog. This sheet has almost all the cells locked except where I need to permit the user to change input values.
When I use the embed code, the sheet just works fine and allows user to make changes to unlocked cells.
However, when I use javascript for the same, I cannot change the unlocked cells. See here –
What could be wrong? How can I allow unlocked cells to be edited on this page? Javascript is using &wdAllowInteractivity=0&wdAllowTyping=1
Hi Rajesh,
The settings for &wdAllowInteractivity=0&wdAllowTyping=1 look correct. Are the JavaScript and Embed pages using exactly the same Excel file?
Mynda
Yes. Very same file.
Sounds like a bug. I’ll report it to Microsoft. Don’t hold your breath though. Changes will be slow, if at all.
Edited comment
It probably is a bug. I find out that you don’t need WD part from wdAlowTyping alongside action=embedview.
Correct syntax: action=embedview&AllowTyping=1
Thanks for sharing, Tautis!
Hello,
I’m having the same problem. With javascript I cannot change the unlocked cells…
Can you help?
And the options if i want to use macros or VBA, and embed in wordpress?
Thank you
Hi Manual,
The JavaScript issue appears to be a bug, sorry.
As for using Macros/VBA on the web, this is not possible. You have to use JavaScript instead, but this is not fully developed for Excel yet.
Mynda
Thank you.
I think i solved the “bug” when not allow the user type in unlock cells, with the embed javascript. It seems is not a bug, you only change this number, from 0 to 1: “&wdAllowInteractivity=1(here)&wdAllowTyping=1”
Now the user can interact with your page.
Thank you
Could you please type here, for an easy copy/paste, the example HTML that contains the JavaScript code necessary to hide the viewer bar?
Thank you!!!
Hi Manuel,
Are you having trouble downloading the html file linked to above?
Mynda
Hi Manuel,
That’s already linked in the post above.
https://d13ot9o61jdzpp.cloudfront.net/files/moth_embedded_workbook_js.html
The JS is at the bottom of that file in between the <script></script> tags
Sorry,
I can´t do it…i don´t know why. When i click on those links only shows the embed doc, nothing else.
How i can download the html file to hide the viewer bar?
Thank you!
Right click the link and then ‘Save as’ then open the file for editing.
How to set the date format for the embedded excel sheet. I use DD/MM/YYYY. However, on my webpage, it shows as MM/DD/YYYY.
How can I set to DD/MM/YYYY?
Hi,
I was able to solve this by changing the date format from *dd-mm-yyyy to dd-mm-yyyy.
I have to come back again here. While the above solved the date display issue which now displays correctly in DD/MM/YYYY format, the input date field when selected reverts to MM/DD/YYYY format.
How can I correct this?
Hi Rajesh,
I seem to recall that the embedded workbook date format is always mm/dd/yyyy. I think this is because it’s OneDrive in the cloud. You could try changing your OneDrive settings, although I’m not sure how you do that, or if it’s even possible.
Mynda
Thanks Mynda. I will look for it.
Hi Mynda,
Changing the setting in OneDrive, corrected the display to DDMMYYYY. However, when the cell is selected for input, the format changes to MMDDYYYY, which is even more confusing.
Anyway to address this?
Hi Rajesh,
No, I usually succumb to mm/dd/yyyy format because it’s the only format that will consistently display. I add a note above the cell or as a data validation tip for the user.
Mynda
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?
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
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
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
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
Hi Willem,
In the Options you’ll find the settings. File tab > Options > Advanced > Display Options for this workbook.
Mynda
Hello! The JavaScript button is gone. Some option for the embed to hide the bar? Thank you.
Hi Aurélio,
I think it’s a bug in OneDrive. I’ve notified Microsoft and am hoping they fix it soon.
Mynda
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….
Microsoft are working on a fix. I expect it will be a few weeks away yet.
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
There’s no other way that I’m aware of, sorry.
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?
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
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.
Hi Jason,
I’ve emailed you a working solution. Let me know if you don’t get that.
regards
Phil
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
No worries Jason 🙂
Glad to help.
Phil
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!
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
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
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
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
Good find, Bax. Thanks for sharing!
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?
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
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?
Hi Orin,
I haven’t come across this before. Have you tested this in a different browser?
Mynda
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!
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
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?
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
Yeah that’s what I think, but what if I make my excel file as a webpage will it be published with macro/buttons?
No, macro’s and ActiveX controls are not working in browser.
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.
You do have the instructions in this page, you just have to read it: hide viewer bar
Cheers,
Catalin
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
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
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 🙂
Hi Joshua,
Here’s your 2 workbooks without viewer bars.
Joshua’s WB’s
regards
Phil
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 🙂
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
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
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
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.
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
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
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
What a great tutorial!
Searched the entire internet on hiding the lower excel bar: NOTHING! Except here!
And it works great!
Thanks a lot!
Glad we could help, Nico 🙂
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
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
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
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
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
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
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
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
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
Thanks very much for the response, clear explanation! 🙂
No worries 🙁
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.
🙂 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
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!
Cool Shayne. Just wanted to make sure you knew this ‘protection’ wasn’t too hard to circumvent 🙂
Regards
Phil
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.
Hi SS,
Yes, anyone can get your file and no, there’s no way to protect it using the WebApp.
Kind regards,
Mynda
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?
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
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!! 🙁 🙁
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
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… 🙂
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
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
Thank you, Cristian 🙂
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
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
Why is the embedded excel sheet in this page not interactive any more?
Microsoft have broken it! They’re working on a fix as I type. Please check back later to see if it’s working.
Mynda
It’s working again 🙂
yayyy 🙂
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
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
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):
Thanks very much for your considerable effort over a long period on this.
Cheers
Steve
You’re welcome, Steve.
Hi Mynda and Phil,
Has something just changed in onedrive? My embedded spreadsheet no longer shows and neither does yours!
Cheers
Steve
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
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
OK no worries, glad it’s all back and working 🙂
Phil
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}.
*/
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
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?
Hi Igor,
I’ll email you directly and see what we can do to help.
Cheers
Phil
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 🙂
Great work Igor, thank you.
Phil
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?
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
Hi Phil,
Would that support multiple embedded excel files?
Thanks 🙂
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
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
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
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!
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.
Hi Pavel,
Sorry I don’t know why this would happen. Hopefully Microsoft will fix this issue too.
Regards
Phil
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 !
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
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
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
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?
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
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
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
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.
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
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?
Hi Ben,
You can use Excel Survey to do that:
https://www.myonlinetraininghub.com/excel-surveys-an-easy-way-to-collect-data
Mynda
Hello,
I have a new problem.
Some users can see it without problems. But some users can’t.
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?
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
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.
Hi Pavel,
Maybe! Hopefully whatever the cause of the problem it has been rectified and won’t return.
Regards
Phil
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?
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
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
* https://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 https://msdn.microsoft.com/sv-SE/library/hh315812.aspx.
*/
document.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = “none”;
}
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
Hi Phil! Sure! On this page you can see how the Embedded and the JavaScript version displays differently:
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
* https://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 https://msdn.microsoft.com/sv-SE/library/hh315812.aspx.
*/
document.getElementById(“ewaSynd1_m_ewaEmbedViewerBar”).style.display = “none”;
}
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!
Thanks Gerrie 🙂
I’m getting similar issues, I’ll check into it and get back to you.
Phil
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..
Hi Steve,
Did you manage to get it working again? Was it a WordPress issue or MS embedded javascript issue?
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!
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
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
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.
Hi Beau,
I’m not aware of that. My dashboard above is still working.
Have you checked the file is still on OneDrive?
Mynda
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.
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!
Hi Simon,
Concurrent user access is handled by the Excel web app and according to this article https://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
Hi,
I followed the direction, but i could not hide the viewer bar. any advise.
thx
Sait
Hi,
Please provide a link (URL) to the webpage and I’ll check your code.
regards
Mynda
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
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
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
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
LOL, Hi Steve. Yes the internet can be funny like that sometimes.
Glad we could help you out.
Phil
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
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
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
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
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
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
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.
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
I hope that helps.
Kind regards,
Mynda
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
Hola Ángel,
Lo siento que no tengo ninguna plantilla que va a hacer lo que quieres.
Saludos cordiales,
Mynda
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!
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
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!
Oh, good. Glad you found a workaround.
Mynda
Hi Mynda,
Just wondering, do you offer a course on creating dashboards using Power BI for Office 365?
Thanks!
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
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
Brilliant, Ivan 🙂 glad to be able to help.
Mynda
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.
Awesome, thanks Rojas 🙂
Superb stuff Phil & Mynda,
Thanks for a lovely tutorial… I just keep growing more and more indebted to MyOnlineTrainingHub.
Cheers,
Adi
Cheers, Adi 🙂 Glad you liked it.
Mynda.
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.
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.
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.
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.
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.
Hi Henk,
Great to know you cracked it 🙂
Mynda.
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
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.
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.
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.
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.
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.
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.
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
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.
I just tried the JavaScript part and it just worked. Thank you again!
Great! Good job 🙂
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 🙂
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 hope that helps.
Mynda
Hi Maxime,
If you have problems with the JavaScript let me know and I’ll see if I can help.
Phil
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.
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
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?
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
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.
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
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!
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.
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 :-/
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.
Hi Mynda
This is an awesome post. I learned so much since joining your excel course.
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.
Cool article. I am sure many users will find this very helpful and save them a lot of time. Thanks for sharing.
Cheers, Jef 🙂
Hi Mynda,
What an Excellent Post. I admire you most!
Thanks! 🙂
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!
Also my spreadsheet is all homegrown edit Formulas of countif / index / match, so no hidden VB Macro!
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
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
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