 |
|
|
One extremely handy feature that Excel offers is its ability to allow you to publish a spreadsheet on the web, so you can communicate your data to anyone you like, irrespective of whether they use or even have Microsoft Excel on their computers. In other words, your Excel spreadsheet can be viewed in a web browser. This could be useful for such things as employers wanting their employees to access things such as sales data from different areas, cost calculations, time sheets and many other uses. Basically, if your Excel Workbook is placed on the web, people anywhere in the world can access it, with or without Excel.
When you save a Microsoft Excel workbook or part of a workbook or worksheet as a web page, you can make it available on an HTTP site, and FTP site a Web server or a network server for users to view and/or interact with.
Setting up a web page in Excel involves a two sided approach where you will need to switch between the developmental stage and preview. You are able to preview the web page before final publishing which will allow you to test your publication of data and ensure that the web page is exactly what you require.
Let's firstly have a look at the process of placing an Excel workbook on the web. The first thing that we are going to do is to open the attached workbook WBDLesson8Level3-ExcelontheWeb so that we have some data available to us that we can work with.
When you choose to place all of the data in a workbook onto a web page at once you have the choice of placing either an interactive or noninteractive, or static version of the workbook on your web page.
We are going to use both of these methods to save our workbook to the web, using the noninteractive of static method first.
Interactive Workooks
If you wished to publish your workbook as a web page and you then wished a user to be able to interact with your workbook then you would need to save your workbook as an Interactive workbook. This means that the workbook is saved as an HTML (Hypertext Markup Language) file that contains within it special components that allow a user to do such things as manipulate data, format data, change formulas, switch between sheets to make changes etc. To view a Web page that was saved with interactive data and have all the text appear correctly, you must use Microsoft Internet Explorer 4.01 or later and have the Microsoft Office Web Components installed.
Note: The interactive components used in the HTML file cannot be opened and modified in Excel, so you should maintain a master copy of the Excel workbook from which you published so that you can make changes to it and republish the workbook if necessary.
Noninteractive Workbooks
If you wished a user to have no interaction with your workbook, then you would need to save your workbook as a noninteractive. This means that the data appears as it would in Excel, including tabs that users can click to switch between worksheets. The user would have viewing rights only and the workbook would be static.
Note: Saving an entire workbook as an HTML page is beneficial when you don't want to maintain a master copy of the Excel workbook, but rather want to be able to open the resulting HTML file directly in Excel and make and save changes using Excel features and functionality
Publishing to the Web
Publishing to the web from Excel is very similar to saving your data to a different workbook, and in fact the command Save as Web Page is found under the File menu in the same vicinity as the other Save commands available in Excel. The only difference is that Excel requires more information for web publishing, than it does for other "saves".
Let's now publish our web page as a noninteractive or static web page.
Ensure that 2001 Forecast is your active worksheet.
Select File>Web Page Preview
The workbook will appear in Preview mode in a web browser. Note the workbook-like tabs at the bottom of the browser window work the same as in your workbook. Each sheet in the workbook is stored on its own HTML page. The HTML pages have not yet been saved as HTML files, so this preview page will be stored in the Windows temporary folder.
Select File>Close to close your browser and return to Excel.
Select File>Save as Web Page
When your Save As dialog box comes up, the Entire Workbook option will be the default. We actually want to check the option Selection: Sheet, so lets do this now.
Note under this option there is an option that says: Add Interactivity. We will leave this blank at this stage.
Now click on the button called Change Title
You are now looking at the Set Page Title dialog box, which enables you to give your web page a name. This name will be displayed in the blue title bar of your browser. Let's click in the box and name our web page 2001/2002 Forecasts. N
Note that you are not saving your web page as 2001/2002 Forecasts, you are only giving your page a name. It is very helpful to use this option and to give your web page a meaningful name, as it is the words in this title bar that are used by search engines to pull up web pages, not the name of the actual file.
Select OK
Now notice to the left of the Change Title button that you now have your Page Title: 2001/2002 Forecasts
Now select Publish. This will display another dialog box - Publish as Web Page
Under the Choose: item, notice that Sheet All Contents of 2001 Forecasts appears.
Click on the drop down arrow to the right of the Choose: box and have a look at some of the other items in this list while you are here.
Moving down the dialog box, note that there is a button Add Interactivity which we will leave unchecked at this time.
Check the option Open published web page in browser until it appears with a tick. This will enable you to view your web page in a browser after you publish it.
Click the Publish button
This will take a minute or so. But once completed you should be looking at your published web page, complete with scroll bars.
Adding to Existing Web Pages
When a workbook is published on the web, you must decide precisely what you want to publish from your workbook.
Workbooks in Excel are three dimensional - they contain rows and columns on each worksheet and many worksheets per workbook. Anything can be published from a workbook; ranges, worksheets, pivot tables, charts etc., however you need to bear in mind that web pages are only two dimensional. This means that you can publish various items on one web page, or you can publish items to their own separate web pages which can later by hyperlinked to form a web site. So we have the choice of adding items to our existing web page, or to save items into a separate web page to be linked at a later stage using web tools.
We are going to add to our existing web page. To do this, follow these steps:
Select File>close to leave your browser and return to Excel.
Make sure that 2002 Forecasts is your active worksheet
Select File>Save as Web Page
This time, we need to make sure that Entire Workbook is the option chosen
Click on Publish
You will now have the Publish as Web Page dialog in front of you, and Sheet All contents 2002 Forecasts should be highlighted.
Click on Publish
Because we are using the same default name as before, Excel will now show a message asking if we wish to overwrite the previous file. We do not wish to do this, but we do wish to Add to the file. Let's click the Add to file button.
Select File, then Close
Now we are going to add our Chart to our web page, so to do this follow these steps:
Select File>close to leave your browser and return to Excel.
Make sure that 2001 Expenditure Chart is your active worksheet
Select File>Save as Web Page
This time, we need to make sure that Entire Workbook is the option chosen
Click on Publish
You will now have the Publish as Web Page dialog in front of you, and Chart Chart Sheet (column) should be highlighted.
Click on Publish
Because we are using the same default name as before, Excel will now show a message asking if we wish to overwrite the previous file. We do not wish to do this, but we do wish to Add to the file. Let's click the Add to file button.
Select File, then Close
The final step that we have to now do is to select File then Save to save our workbook in Excel.
Publishing an Interactive Web Page
When you create an interactive or static web page, you are only providing information to clients, staff, vendors, suppliers or whatever. This is great for viewing information, but if you need these users to have input into the web page, you will need to save your web page as an interactive web page so that other people can interact with it as if it were a mini-worksheet.
There is only one requirement when you are creating an interactive web page and that is that the data must be from a specified range in the worksheet. This range is then duplicated, together with the underlying logic of the formulas, in the web page. Once an interactive web page is saved, you will notice that that the duplicated data looks like a down-sized version of Excel.
Lets have a look at saving an interactive web page now.
Close the workbook that you currently have open.
Open the workbook called WBDLesson8Level3-ExcelontheWeb2
Highlight the range A1:F21
Select File>Save as Web Page
Make sure that the option Selection $A$1:$F$21 is your default
Click in Add Interactivity until it appears with a tick
Double click on the word Page under File name: and change it to Car Loan Calculator
Click on Publish as Web Page
this will the display the Publish as Web Page dialog box.
Click on Open published Web Page in browser until it appears with a tick
Select Publish
As your interactive web page appears as a down-sized version of Excel, you may need to maximise your screen to see as much of the page as possible.
Now, let's make sure that it works, and is truly an interactive web page.
Click in cell B3 of your browser window and type in 25,000 then click Enter
Note how your formulas have updated and all your data has changed accordingly.
Select File>Close. This will close your browser and return you to Excel.
Select File>Save
Select File>Close
So, as you can see publishing an Excel workbook on the web as either an interactive or noninteractive web page is an extremely useful tool. It will enable users to view your information on any platform using a standard browser. You can add more items to a web page as required, or you can create separate web pages for each item in a workbook. If you then wanted to link these web pages to form a website however, you would need to use web page software to do so.