Excel VBA: Managing Files and Data

Course details

In this course, Curt Frye—president of Technology and Society, Incorporated—guides you through effective ways to simplify your management of files and data with Excel VBA, a robust and flexible programming environment for Microsoft Excel workbooks. Using VBA to automate repetitive data import and management tasks can save you substantial time and streamline your processes. Learn how to create a filter, chain criteria using AND, and combine criteria using OR. Determine if a file or folder exists, open and close workbooks, save workbooks under new names, and as CSV files. Create and rename worksheets, copy and move worksheets within and between workbooks. Plus, learn to use built-in functions, create and manage UserForms, and manage PivotTables using VBA. Combined with techniques to create and manage charts, work with PivotTables, and capture data using User Forms, file management in Excel VBA will greatly enhance your Excel workflows.

Learning objectives
Understand how to import and export data using Excel VBA.
Articulate how to create and manage workbooks using Excel VBA.
Determine how to create and manage worksheets using Excel VBA.
Examine how to utilize built-in functions in Excel VBA.
Adapt methods to manage charts using Excel VBA.
Implement ways to manage PivotTables using Excel VBA.

Concepts

0. Introduction

01 - Automate repetitive tasks using Excel VBA
02 - Manage files and data using Excel VBA

1. Focusing Data Using Filters

03 - Select the active region
04 - Create a filter
05 - Filter based on more than one field
06 - Combine criteria using OR
07 - Find unique items using xlFilter
08 - Restore a range or worksheet to an unfiltered state

2. Managing Files Using VBA

09 - Determine if a workbook exists
10 - Determine if a folder (directory) exists
11 - Detect whether a file is open
12 - Open a workbook
13 - Close a workbook
14 - Save a workbook under a new name
15 - Save a workbook as a CSV file

3. Managing Worksheets Using VBA

16 - Check if a worksheet exists
17 - Create and rename worksheets
18 - Copy a worksheet within the active workbook
19 - Copy a worksheet to a new workbook
20 - Copy a worksheet to an existing workbook
21 - Move a worksheet within the active workbook
22 - Move a worksheet to a new workbook
23 - Move a worksheet to an existing workbook

4. Taking Advantage of Built-In Functions

24 - Use the built-in Open dialog box
25 - Suppress and restore alerts
26 - Calculate data using Excel worksheet functions
27 - Use the current date and time
28 - Remove spaces from before or after a string

5. Managing Charts in VBA

29 - Create a chart
30 - Move a chart to a chart sheet
31 - Add or remove data series from a chart
32 - Export a chart as an image
33 - Create a line sparkline
34 - Create a column sparkline
35 - Create a win loss sparkline
36 - Delete a sparkline

6. Creating UserForms

37 - Create a UserForm
38 - Add a TextBox to a UserForm
39 - Add a ListBox to a UserForm
40 - Add a ComboBox to a UserForm
41 - Add an option button to a UserForm
42 - Add graphics to a UserForm
43 - Add a SpinButton to a UserForm
44 - Create a multipage or multitab UserForm
45 - Write UserForm data to a worksheet
46 - Run a UserForm

7. Managing PivotTables Using VBA

47 - Record a PivotTable configuration
48 - Assign a PivotTable macro to the Quick Access Toolbar
49 - Advance one step forward in a PivotTable playlist
50 - Move to any PivotTable playlist position

8. Incorporating New Excel Functions

51 - List unique values from a data set using UNIQUE
52 - Limit data displayed in a worksheet using FILTER
53 - Change the order of data using SORT and SORTBY
54 - Copy data from a list using TAKE
55 - Delete data from a list using DROP

9. Continuing Your Excel VBA Learning Journey

56 - Further resources