Home » e-Learning

System iNetwork e-Learning

Microsoft Excel and Microsoft Word for IBM i Developers

You can create visually stunning and easy-to-use modernized applications using Microsoft Excel and Microsoft Word. Excel and Word are two of the most widely used end-user applications. Many people use Excel and Word as their primary tools for working with data. In this System i Network eLearning course, you'll learn techniques to integrate these Microsoft Office applications with your IBM i database. You'll see how you can access data on your IBM i and bring it into Excel and Word, and you'll also learn how to create data-driven forms that let users enter data that is posted to your IBM i.

Learning how to integrate Excel and Word with your IBM i database is one of the quickest and most appreciated ways to modernize your users' access to your IBM i. Rather than have users struggle with making database connections, you can give them prepackaged Excel and Word documents that provide the exact access they need to your IBM i.

Schedule:

Section 1419
Weeks:5
Sessions:5
Start Date:March 16, 2010
End Date:April 13, 2010
Other Important Dates: Starts Tuesday, March 16
Meets:

This class will meet on Tuesdays at 2:00 p.m. EST on March 16, 23, 30, April 6, and 13 using live WebEx meetings. In order to view the WebEx meetings, you need to allow the WebEx application to install software on your PC to view the meetings. You also need speakers or headphones for your PC, as the audio narration is delivered using VoIP.

Each weekly meeting is approximately 1 hour in duration. All of the meetings are recorded, so if you are unable to attend the meeting at the scheduled time or if you want to review the meeting later, you can access the recorded version of the session at your convenience.

A web page for the course is your main point of access to all of the links for the course and your access to the course materials. Each week before the meeting, materials for that week are posted to the course web site and you receive a reminder email advising you to download the materials. The materials that you can access and download from the course website include password-protected PDF files of the course content and links to the recorded versions of the sessions.

The PDF files that you can download include the presentation that is shown during the WebEx meeting and labs and other documents that will help you work through the examples. Although you are not required to work through the labs, you will get the most benefit from the course if you can find time on a weekly basis to complete the labs. All of the labs require the use of the software that is installed either on your PC or within your environment.

Pricing:

$315 for System iNetwork Pro VIP Members. All pricing is based on the cost for System iNetwork Pro VIP Members. If you're not a Pro VIP-level member, you may need to renew or upgrade your subscription status to take any e-class at VIP-level rates. Rates for non-VIP members are $399, which includes a FREE automatic upgrade to Pro VIP status for 12 months (which also means that you'll enjoy VIP-level pricing for the next 12 months!). The rate for international registrations is $499.


To Register:

Register for Microsoft Excel and Microsoft Word for IBM i Developers.

Instructor:

Craig Pelkie
craig@web400.com

Craig Pelkie has worked as a programmer with IBM midrange computers for many years. He has also written and lectured extensively on AS/400 and iSeries technologies, including client/server programming, Client Access, Java, WebSphere and Web development.



Course Agenda:

Course Session Summaries

Meeting 1: IBM i Access for Windows Database Providers and Tools

In this session, you'll learn about the no-charge ODBC and OLE DB database providers that are included with IBM i Access for Windows. The database providers are used to get data from your IBM i into the Office applications and to enable your Office applications to add or update data that is on your IBM i. You'll also learn about the IBM i Access for Windows Excel add-in and the IBM i Access for Windows Data Transfer To and From Excel programs.

Meeting 2: Excel Session 1 - Getting data from your IBM i into Excel

This meeting shows you several techniques to get data from your IBM i into Excel. You'll see that you can bring data directly into Excel, or you can create data files in the IBM i IFS that can be imported into Excel. Among the techniques you'll learn are: using the IBM i Access for Windows Excel add-in; using the IBM i ODBC and OLE DB providers to access data in Excel; creating Data Queries in Excel; working with CSV stream files as import files.

Meeting 3: Excel Session 2 - Create Excel forms to work with data on your IBM i

Now that you know how to get data from your IBM i into Excel, you'll learn how to create Excel forms. Using Excel forms, you can display data from your IBM i or you can enter data into Excel and add data to or update your IBM i database. You'll learn about the Excel visual form controls that you can use and see how to connect those controls to the database providers. You'll see how you can create visually appealing and easy-to-use applications that provide a modernized look-and-feel for your IBM i.

Meeting 4: Word Session 1 - Getting data from your IBM i into Word

This session shows how to use the data access tools that are provided with Microsoft Word, including the Mail Merge features used to produce form letters, envelopes and labels. You'll learn how to create and work with merge fields and how to use Microsoft Query to get data from your IBM i into Word. Using Word, you can create report applications that make full use of Word's typographic features (fonts, font sizes and settings, color) and include graphics in the report.

Meeting 5: Word Session 2 - Create Word forms to work with data on your IBM i

Using Word, you can create data-driven forms that let your users access your IBM i. You can create data entry forms, update capable forms and forms that let users enter prompts for reports and other batch processes. Word forms let you create easy-to-use applications that your users will truly appreciate and provide you with a quick way to put a modernized front-end on your IBM i.

Prerequisites:

You should have an IBM i programming background to take this course. This course assumes that you are familiar with IBM i database principles, including libraries, physical files, logical files and file members, in addition to record formats and fields and their characteristics (for example, the differences between alpha and numeric field data types). You do not need to have previous Microsoft Office programming experience, but it will be helpful if you have at least entry-level experience using Microsoft Word and Microsoft Excel. Some of the techniques described in this course use simple SQL statements to access your IBM i database, so any familiarity you have with SQL will be helpful.

Minimum Technical Requirements:

The following software is used in this course. You are responsible for installing and configuring the software on your PC; System i Network and Craig Pelkie (the instructor for this course) cannot provide any of the required software for you. The software that is used is available from both Microsoft and IBM. The course labs include instructions that show you how to install and configure the IBM ODBC and OLE DB providers. You are responsible for installing the Microsoft Excel and Microsoft Word products, no install instructions are presented for those applications.

The required software includes:

IBM i Access for Windows database providers, including the ODBC Driver and the OLE DB Provider. This requires iSeries Access for Windows V5R3 or above. You will need to ensure that you have installed the most currently available Service Pack for your version of Access for Windows. You can use any of the following versions:

  • iSeries Access for Windows V5R3
  • System i Access for Windows V5R4
  • IBM i Access for Windows V6R1

Your IBM i can be running any V5Rx version of OS/400 or the i 6.1 operating system. You will need an IBM i user profile that has sufficient authority to create and manage libraries and database objects on the IBM i. If you are running an older version of OS/400 (for example, V5R1 or V5R2), you will need to obtain and install at least the iSeries Access for Windows V5R3 version on your PC. Meeting 1 provides complete instructions on how to install the required IBM i Access for Windows software, including how to download and apply the current Service Pack.

For Excel Session 1 and Excel Session 2:

  • Microsoft Office Excel 2003 or
  • Microsoft Office Excel 2007
For Word Session 1 and Word Session 2:

  • Microsoft Office Word 2003 or
  • Microsoft Office Word 2007

You can download a 60-day no-charge evaluation copy of one of the Microsoft Office suites from Microsoft's Office website at http://www.microsoft.com/office. Note that in order to download an evaluation edition, you need to complete a short no-charge registration form with Microsoft.

Note, this course describes features that are available in the Microsoft Word 2007 and Microsoft Excel 2007 products. If your company is using Microsoft Word 2003 or Microsoft Excel 2003, you should be aware that some of the features that are described in this course will not be available in your version. This course does not provide details about features that are available or missing in earlier versions (for example, Microsoft Office XP or Microsoft Office 2000).

If you have a specific question regarding course content, contact Craig Pelkie at craig@web400.com.

FAQs specific to this course:
No FAQs for this course.

What about the e-learning experience?
Here's a list of frequently asked questions about System iNetwork e-learning classes.

ProVIP Sponsors