Integrating and Processing Data in Microsoft Excel and VBA Workshop Outline

Our Excel Data Integration workshop covers how to turn Microsoft Excel into a sophisticated data integration and data processing platform. Learn how to integrate and process data from multiple workbooks, worksheets, text files, databases and other programs using built-in Microsoft Excel tools, VBA, SQL and ADO. Do things with your data that you could only dream about before. Read below and see how our 14 years of Excel training and software design can help supercharge your Excel VBA skills >>>

Call us at:   1.805.498.7162     1.805.558.9277--

• Onsite, Self-Study*, Public*, Webinar*

*Advanced VBA covers the same basic material in a less focused format. Click here for business. Click here for engineering and science.

In the world of today, data storage has become spread out over various programs, databases and file types. You need one system to talk to another or several others to get the full answer. This data integration however, cannot be performed through the existing software due to expense or lack of capability. Microsoft Excel tools along with Visual Basic for Applications (VBA) can be used to first integrate these data sources automatically then process them. You will find that the combination of both these environments allow data tools to be developed that can for example integrate 3 workbooks, 50 text files, and 50 Access databases (an actual problem for EMAGENIT) making them behave as one uniform data source.

In this 3-day "hands-on" workshop you will learn:

Day-1 (The Basics)

• What is a query?

• What is a relational database and how is one assembled

• How to design worksheet tables in Excel workbooks that can be queried like relational databases or used by VBA

• How to write basic SQL commands that are used in queries (important for both Excel tools and VBA)

• How to use Microsoft Query to query text files, Excel workbooks and databases

• How to tie into Microsoft Access from Excel

• Review of key Microsoft Excel worksheet functions used in data analysis

• How to create advanced worksheet formulas that integrate data (you do not always have to use VBA to get the job done)

• Review of AutoFilter, Advanced Filter, the Form tool, Text To Columns and Pivot Tables in prelude to VBA automation

• Key strategies for automating Excel's data tools in VBA

Day-2 (How to in VBA)

• How to control a range with VBA while accessing and filling in worksheet data

• How to use key worksheet functions and Excel tools to search for worksheet data in VBA

• How to control workbooks and worksheets with VBA

• How to control AutoFilter, Sort, the Advanced Filter, Text To Columns and Pivot Tables with VBA

• Review of key looping structures and strategies in VBA that are used to access files, worksheet data and Excel tools

• How to control dashboard drawing shapes and charts using VBA

• How to access Window's file system using VBA including multi-file processing

• How to control other programs using VBA and how to search out the information in Google

• How to use VBA to control Microsoft Query

• How to import, clean and parse text files using VBA, the FileSystemObject, ADO and OLE DB

Day-3 (Build a Functional System)

• How to use VBA to control DAO, ADO and OLE DB. These entities can talk to databases, text files and Excel files.

• How to access Windows file management dialog boxes using VBA

• In-class project laying out and building an operational data processing system using the above techniques. Class votes on project.

• Professionals with beginning Excel VBA experience and a solid understanding of how Microsoft Excel works.

Submit a question to us via email at dataintegration@emagenit.com

Call EMAGENIT between the hours of 8:00 A.M. and 5 P.M. PST at 1.805.498.7162 or 805.558.9277 for more information about this workshop.