Excel Training, Excel VBA Training, Excel Software Design

Microsoft Excel VBA Data Mining
for Combined Audience

Microsoft Excel VBA Data Mining
Onsite Only

Our Microsoft Excel VBA data mining training shows you how to turn Microsoft Excel into a supercharged data processing tool using its data tools, advanced visual features and Excel VBA.

Our 3-day "hands-on" Excel VBA data processing training shows you how to turn Excel into an advanced data mining system. Our workshop shows you how to use VBA to automate Excel's data processing features in unique ways. It discusses how to use loops and logic to reconcile data in multiple workbooks from multiple worksheets from multiple folders. Our workshop also demonstrates how to distribute data into different table formats and templates. It also demonstrates how to use Excel's text file features and worksheet functions with VBA to data mine hard to read text files and workbooks. Our workshop also shows how to generate reports in multiple workbooks/worksheets and creating folders for them. It also presents how to use Excel VBA's database tools to process data. Excel for Mac users welcome.

Microsoft Excel VBA Excel Data Processing:   Key Skills You'll Learn

Microsoft Excel VBA Excel Data Processing: Key Skills You'll Learn
  • What is Excel VBA?
  • A complete VBA language review including how to use variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling in relation to data processing
  • Microsoft Excel data analysis tool review (autofilter, advanced filter, sort, Microsoft Query, pivot tables)
  • Review of key Microsoft Excel worksheet functions that process or find data
  • How to control key Microsoft Excel objects that relate to data processing with Excel VBA
  • How to use key Excel worksheet functions in VBA that process data
  • Excel VBA data processing techniques for sorting, filtering, copying, pasting, inserting and deleting worksheet data
  • Excel VBA data processing techniques for inserting and deleting worksheet columns
  • How to use Excel VBA loops to process data on multiple worksheets and in multiple workbooks
  • Excel VBA data processing techniques for controlling the Windows file system
  • How to use Excel VBA to automate pivot tables
  • Excel VBA data mining techniques for opening, scanning and processing text files
  • Excel VBA data mining techniques for processing hard to read worksheets
  • How to use Excel VBA to read raw data and fill in specific table formats
  • Excel VBA data mining techniques using ADO, DAO and SQL

Microsoft Excel VBA Data Mining:   Who Should Take Our Training

Microsoft Excel VBA Data Mining:  Who Should Take Our Training

Select this workshop if you or your group have:

  • Have never programmed or just used the macro recorder
  • Used Microsoft Excel's data features and know them operationally
  • Formatted worksheets and know Excel's formatting capabilities
  • Have used Excel's worksheet functions like SUMIF, COUNTIF, Match..

Microsoft Excel VBA Data Processing:   Workshop Outline

Important: We focus our training on what our customers need. When class begins, we analyze those needs and shift our workshop training outline appropriately. We will stress topics or add topics that our customers want. No two workshops are ever the same with EMAGENIT.



Day-1

Visual Basic Editor Review

Free Repeats Policy
  • A complete review of the Excel VBA Editor and its windows
  • What is a project, module and a procedure and how are they used in beginning Excel VBA
  • How to use the VBA Editor's debugging tools to correct your computer code

Full Excel VBA Language and Programming Review

  • A complete beginning VBA language review including how to use variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling in relation to data processing/data mining
  • How to write a program in VBA, how to translate your ideas into a program that combines Excel with VBA
  • What are an object, property and method and how do you use them in beginning Excel VBA
  • How to control Microsoft Excel with VBA. In other words how to figure out and actually use the command.command.command VBA syntax
  • What is logic and how to translate your task into logic in your VBA computer code. A complete review of using logic like IF and Select Case in your programs

Controlling Cells, Ranges and Changing Data Table Size in Your Excel VBA Data Processing Programs

 
  • How to identify and use cells and ranges in your Excel VBA data mining code
  • How to use Excel VBA to copy and paste worksheet data
  • How to use VBA to gather worksheet data, perform calculations and output to a worksheet
  • How to use VBA to control cell fonts, colors, borders, number formats and alignments
  • How to use Excel VBA to automatically format a resizing table on your worksheet
  • How to use VBA to perform common worksheet data tasks like deleting, inserting and moving columns, rows and cells

How to Perform Calculations in Your Excel VBA Data Processing Program

  • How to perform calculations in your Excel VBA code
  • How to run VBA functions and Excel worksheet functions in your code
  • Review of key VBA and worksheet functions that are vital to data mining
  • How to analyze dates and times using VBA functions and worksheet functions
  • How to use Excel VBA to automatically map range and cell references

 Controlling Workbooks, Worksheets and Key Excel Data Tools in Your Excel VBA Data Mining Programs

  • How to use Excel VBA to create, open, save and close workbooks
  • How to use Excel VBA to add, move and rename worksheets
  • How to use Excel VBA to control Excel's data processing filters like Autofilter, Advanced filter and Remove Duplicates
  • How to use Excel VBA to control the Sort feature, Text-to-Columns feature and grouping features
  • How to control Pivot tables using Excel VBA


Day-2

How to Find Data Workbooks, Worksheets and Cells in Your Excel VBA Data Processing Program

  • How to use Excel VBA to control Excel's interfaces, like the Open dialog box, to find and open your data workbooks
  • How to use the FOR...Each Next loop to search for data workbooks and data worksheets based on their characteristics
  • How to automate the worksheet Find feature in Excel VBA to find cells without knowing their location
  • How to use the Set statement in VBA to track your workbooks, worksheets and cells

How to Use VBA Loops and Logic to Scan for Data in Your Excel VBA Data Mining programs

  • How to organize your data in properly designed tables on your Excel worksheet
  • How to use VBA loops and logic to process data on a single worksheet
  • How to use VBA loops and logic to process data on multiple worksheets
  • How to display your results in different workbooks and worksheets
  • How to use Excel VBA loops and logic to fill out a form from worksheet data
  • How to use Excel VBA loops and logic to fill out a specific table template from worksheet data


Day-3

How to Use Excel VBA to Control the Windows Registry, Folder and File Systems

  • Why use the Windows Registry to store your Excel VBA data processing program information
  • How to store and retrieve information from the Windows Registry
  • How to use Excel VBA to create, move, rename and delete Windows folders
  • How to detect if files or folders exist in your Excel VBA data mining programs
  • How to scan for folders and files quickly in your Excel VBA data processing programs
  • How to use the Split and Instr VBA text functions to read your file and folder names
  • How to loop through, open and close hundreds or even thousands of workbooks in different folders using Excel VBA

How to Use SQL, ADO and DAO in Excel VBA Data Mining Programs

  • SQL language review
  • How to use string concatenation and loops to assemble a SQL statement in your VBA code
  • How to use DAO to control Microsoft Access databases
  • How to use ADO to control SQL driven databases
  • How to use ADO to query and process data in workbooks and text files

How to Control Text Files in Your Excel VBA Data Processing Programs

  • How to open and close text files using Excel VBA
  • How to scan text files for information
  • How to clean misaligned degraded text files and parse their information
  • How to get information from large text files (250,000 + rows of data) in to Excel using VBA

Microsoft Excel VBA Data Mining:   Additional Information and Signup

Additional Microsoft Excel VBA Data Mining Training Information

  • Duration: 3-days
  • Our workshops cover Excel versions 2007-2016 including Excel for Mac and discuss differences when necessary.

Included in Our Microsoft Excel VBA Data Processing Training

  • Free Repeats
  • 1800+ page manual filled with key Excel strategies, examples and tips
  • Hands-on training
  • Personal questions answered

Training Formats

Onsite Only

Microsoft Excel VBA Data Processing:   Question Form

Instructions: Please fill in your contact information.

Name:
Company:
Company Web:
Country:
Phone #:
Ext:
e-mail:
Contact me directly by phone

Please fill out the box below with your workshop questions and EMAGENIT will contact you.

Request Workshop Information (Optional)


Send me on-site workshop information Send me self-study manual information
Send me public seminar workshop information Send me live online webinar information

Privacy Policy



Site Map

Copyright © 2002-2017

EMAGENIT All Rights Reserved