IBM i > TIPS & TECHNIQUES > MISCELLANEOUS

Using Excel for Simple Intranet Sharing


Working with clients on a daily basis, I’ve found keeping track of internal and external documentation, external Web links, training seminars, etc., can be a never-ending task. In a quest for an easy solution, I found that Excel, a simple tool available to most of us in our Microsoft Office Suite, can not only track an always growing list of documentation for our systems, coding standards, resource links, etc., but also generate a simple Web-based application we can use on our intranet to help those we interact with daily.

Using Excel, we can organize all our documentation and links using simple procedures, and add a little flair such as a graphic for our Web page and some page-to-page links as well. I’ll go on record as saying that this is not the most glamorous of solutions but is simple and effective, not to mention easily maintained by anyone with basic Excel skills.

Getting Started

In the interest of simplicity, I created a folder on my local E work drive for this exercise. The location of the generated HTML or the documents you’ll provide links to can be anywhere on your enterprise server(s) or the internet.

On my drive, I created the folder HTMLIT. I then created in HTMLIT additional subdirectories, documents, html, sourcecode and xls as shown in Figure 1. You may choose other names as suits your needs

In the xls subdirectory, I want to create a Template.xlsx document to be used as the template for all the Web pages I will be eventually creating so that all my pages have the same look and functionality. Using the Insert tab, I selected the WordArt tab to create a simple page graphic, HTMLIT. I then created simple text boxes to the right of the graphic for the four Web pages that will be used in this article as shown in Figure 2.

Now we can create a few shell Web pages from our Excel Workbook so that we can then edit our text boxes to make them hyperlinks to our pages.

Select the Save As option, then the Other Formats tabs. Click the Save as type drop down and select Web Page. Now click the Publish button as shown in Figure 3. Now change the File name values to represent the location where you wish to store the html and the file name to Home.htm. Make sure the AutoRepublish every time this workbook is saved box is not checked as shown in Figure 4 then click the Publish button. Repeat these steps for each of the remaining shell Web pages you wish to create as we did for our Technical, Manuals and Links pages.

Now back on the Template.xlsx, you can right-click each of the Web page text boxes, select Hyperlink and enter the information to create the hyperlink for the page just created as shown in Figure 5. When all links have been updated (Figure 6), our template is now complete and we can save and close the Excel Workbook.

Our next task is to replicate the Template.xlsx for each of our Web pages, Home, Technical, Manuals and Links, which can be done by copying the Template.xlsx and renaming to the desired page. This will give us the same base look for all our pages.

Building Your Pages

Now double-click the Home.xlsx to begin editing our Home page. I begin by keying the text I wish to have displayed on the page in the cells below the Template items starting in Column A, Row 7. Once entered, select all columns and rows used and click the Format as Table tab under the Styles tab. Select the template you wish to use and check the My table has headers box before pressing the Ok button. As shown in Figure 7, you can see that I added a Date, News and Link column with data. The Link column data is a Hyperlink to the same pages we previously created.

Once done, we need to save our document as a Web page similar to what we did before with our Template document (Figure 7) but this time, we want to check the AutoRepublish every time this workbook is saved is checked Figure 8 so that any changes we make to the xlsx workbook for a page will then regenerate our Web page as well.

Each page you wish to publish can be done using this process and by using the Template.xlsx as the baseline for your page(s), they will have a similar look and functionality.

Displaying our Web Pages

From the drive and folder where you stored your generated html, double-click the Home.htm page to open in your default browser. Our generated Home page is shown in Figure 10 and Links page in Figure 11.

Simple Document Control

By using Excel and its capability to save the contents as a generated Web page, we can create a simple document control intranet website for our documentation, coding standards, code samples and tips for RPG, CL, SQL, etc. In addition, integrating external links from IBM and other websites can be included so there’s one reference point for all staff to use and share.

David Andruchuk is the senior architect for Computer Systems Design Associates Inc.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

IBM i > TIPS & TECHNIQUES > MISCELLANEOUS

Application Testing: Giving Users What They Need

IBM i > TIPS & TECHNIQUES > MISCELLANEOUS

Change Management: Approval Must Be Earned

IBM i > TIPS & TECHNIQUES > MISCELLANEOUS

Making Changes: Prudence and Discipline Always

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters