Transcript KS2 Technologies

Integrate. Automate. eXLerateTM
www.ks2inc.com
www.ks2inc.com
About KS2
IBM Business Partner in metroplex for more than
10 years.
IBM iSeries value added reseller.
Vertical market expertise in Homebuilding/
Construction and Manufacturing/Distribution.
Experience with large packaged applications
including: JD Edwards – OneWorld and World,
Oracle.
Custom e-commerce/web solutions and training.
Custom Excel solutions and training.
© KS2 Technologies, Inc.
www.ks2inc.com
About the speaker
Principal Consultant
10+ years financial and operational reporting
experience
JD Edwards World/OneWorld and other ERP
package experience.
Experienced with Access, Essbase, Crystal
Reports and other reporting tools
Excel 2000/XP, SQL, VB/VBA programmer
© KS2 Technologies, Inc.
www.ks2inc.com
Goals for Today
© KS2 Technologies, Inc.
www.ks2inc.com
Goals for Today
Show the possibilities of integrating the best
spreadsheet tool with the best e-business
server.
Checklist of software you will need to
integrate Excel with the iSeries.
Overview of sample reporting application.
Step through the sample application in detail.
Refine the sample application.
Q&A
© KS2 Technologies, Inc.
www.ks2inc.com
Software Checklist
 Client Software:





iSeries Access for Windows when installing
for the first time, make sure the
ODBC and OLE DB Provider is checked.
- There are also VB wizards and tools you
can install and play with.
- No license required for the odbc/ole db
components.
Install latest PTF to client
Excel 2000+ (Excel 97 will also work, but
we don’t support)
Latest Excel SP (service pack) from
Microsoft
Latest MDAC (Microsoft Data Access Comp
onents) – this has the core odbc/ado drivers

Currently 2.8
 Server Software:

Make sure latest ptf’s are applied on iSeries.
© KS2 Technologies, Inc.
www.ks2inc.com
Overview of Application


When opening the workbook, it will prompt you to enable macros.
If it does not, you may need to change your macro security. Tools | Macro | Security
© KS2 Technologies, Inc.
www.ks2inc.com
Overview of Application
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application

Named Ranges: Insert | Named | Define - best practice used for grouping like cells together so they can be referred to easily – ie. instead of C4 – rngDatabase.
 IBMDA400 is a special string that the OLE DB provider recognizes as an IBM AS/400 data
source.
 Machine Name: iSeries ip address or machine name – this is where the data resides.
 SQL Statement: this is the SQL statement that will be passed onto the iSeries.


Note: the & is the concatenator in Excel – this formula is stringing cells together from down below
Output: this is a named range being used for the starting point (cell) of the data that is
returned from the query.
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application

Standard SQL statement:



SELECT: determines the fields to be included in the query
FROM: choose the library/file to pick the fields from
WHERE: filter based on one or more conditions.

This example is selecting all months from a GL Trial Balance file where the fiscal
year = 2002.
 This statement is referenced in cell C6 for processing in the macro.
 Note: we only picked 1 file in this example. Normally, you will pick the transaction and
do some “look ups” in the master file.
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application


Command Button: View | Toolbars | Forms. This command button is used to launch
the macros. You can assign the button to a macro by right-clicking it and then choosing
assign macro.
There are 2 types of controls – the “old” forms controls that have been around since
the early days of Excel and the visual basic controls – View | Toolbars | Visual Basic.

The forms controls are easier to work with when you are just getting started. As you advance,
you will use the visual basic controls more often.
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application

To get here: ALT+F11 o
r Tools | Macros | Visual
Basic Editor
 Left Pane: shows
the sheets and modules/
forms/classes
 Right Pane: where you
enter and edit/debug
code.
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application

Set a reference to the ADO 2.x library so that the ADO objects can be used in your code
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application


Sub GetData() = name of the subroutine
Dim xxxx as xxxxxx = declaring variables






objFields – field object used for storing the field names
rsRecords – recordset object used for storing the recordset returned from the iSeries.
strSQL – string used to store the SQL statement
lngOffset – used for storing the spreadsheet column position for the field titles
lngColumn – used for storing the spreadsheet column position for the field values in each row.
lngRow – used for storing the spreadsheet row position for the field values in each row.
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application





Objects: items in the application that you can program.
Methods: what you do to the objects (action)
Properties: attributes of objects
Analogy: Noun (Objects) , Verbs (Methods), Adjectives (Properties)
Note: the named ranges are being pulled in from the spreadsheet and used like a
variable. (ie. Range(“rngDatabase”).Value = IBMDA400).
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application


To use (create an instance) of the recordset object, need to first set it.
Once set, open the recordset by passing in the SQL statement, connection info, type of
cursor, and type of command
 Next, add field headers by starting at named range “Output”. Loop through each field
and return it to the spreadsheet.
 Resize and bold the font for the field titles
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application


Set the starting row = 1
Loop through the recordset until the end of file is reached (EOF).

If not at the end of the file then





Loop through each field value in the record and print the current value to the proper cell in the spreadsheet.
Next field value
Advance the row counter
Otherwise, the end of file (EOF) has been reached and notify the user
Move to the next record
© KS2 Technologies, Inc.
www.ks2inc.com
Step Through the Application



Close the recordset
Free up the memory that was used for the recordset.
Finished!
© KS2 Technologies, Inc.
www.ks2inc.com
Refine the Application
Add controls so that the SQL statement can
be dynamic.
© KS2 Technologies, Inc.
www.ks2inc.com
Refine the Application
Update the formula in B12 to pickup the
selections.
© KS2 Technologies, Inc.
www.ks2inc.com
Refine the Application
Create another dropdown for the account #’s.
© KS2 Technologies, Inc.
www.ks2inc.com
Refine the Application
Update the formula in B12 to pickup the
new selection.
© KS2 Technologies, Inc.
www.ks2inc.com
Finalizing the Application
Move the controls to the main reporting
sheet.
© KS2 Technologies, Inc.
www.ks2inc.com
Terminology
 VBA: Visual Basic for Applications: programming language used
primarily in the MS Office suite. (It has been licensed to other apps.)
 VB: Visual Basic: programming language that is easy to learn and
runs on the Microsoft platform.
 ODBC: Open DataBase Connectivity: database access standard created
by Microsoft that is widely used in many different applications.
 Easy to use with no programming required
 OLE DB Provider: Object Linking and Embedding Database Provider–
system level programming interface for accessing all types of data.
 ADO: ActiveX Data Objects: an application programming interface
created by Microsoft to universally access data.
 Requires user written code that can be stored in Office macros.
 SQL: Structured Query Language: a programming language that is used
to read, write, and update database tables/files.
© KS2 Technologies, Inc.
www.ks2inc.com
Resources
Books:
SQL at Work – MC Press
Excel 2002/2003 – Wrox Publishing
Links:
www.mcpressonline.com
www.iseriesnetwork.com
www-1.ibm.com/servers/eserver/iseries/access/oledb
www-1.ibm.com/servers/eserver/iseries/access/sphist
.htm
msdn.microsoft.com/office/understanding/excel/
msdn.microsoft.com/data/default.aspx
© KS2 Technologies, Inc.
www.ks2inc.com
Contact Information
Eric Kuefler
KS2 Technologies, Inc.
4425 W. Airport Freeway Suite 550
Irving, Texas 75062
972.659.9631 x213
[email protected]
© KS2 Technologies, Inc.
www.ks2inc.com
Thank You!!!
© KS2 Technologies, Inc.