Training videos for learning VBA - WORKING WITH DATA

We have 55 VBA - working with data videos listed below. You might also like to consider booking a place on one of our online or classroom VBA courses, where you will meet the owls behind the videos!

Excel VBA Part 30 - Querying a Database with ADO

Posted by Andrew Gould on 31 March 2014

How to write macros in VBA to work with SQL Server, Access and other tables

Excel VBA Part 31 - Modifying Data with ADO Recordsets

Posted by Andrew Gould on 01 April 2014

How to use VBA macros to amend records in SQL Server and Access tables

Excel VBA Part 32 - Executing SQL Commands with ADO

Posted by Andrew Gould on 02 April 2014

How to construct and manipulate command objects in ADO to talk to databases using VBA

Excel VBA Part 56.1 - Connect to SQL Server using ADO

Posted by Andrew Gould on 24 June 2019

This video explains the current Microsoft recommendations for connecting to SQL Server using ADO and the new OLE DB Driver. You'll see how to install the new driver and create a connection string which references it. You'll also see how to create a basic recordset to retrieve data from a SQL Server database and write the results into an Excel worksheet.

Excel VBA Part 56.2 - Get Data from SQL Server with ADO Recordsets

Posted by Andrew Gould on 24 June 2019

This video shows you how to extract data from a SQL Server database using the ActiveX Data Objects (ADO) library for VBA and the Microsoft OLE DB Driver for SQL Server. You'll learn how to write a connection string to connect to a SQL Server databse and how to use a recordset to retrieve data. You'll learn how to populate the recordset using SQL Server tables, views, stored procedures and select statements and how to write the data you've retrieved into an Excel Worksheet.

Excel VBA Part 56.3 - Read and Execute SQL Query Files

Posted by Andrew Gould on 24 June 2019

In this video you'll learn how to open an SQL Query file, read its contents and use the result to populate an ADO recordset. You'll learn how to use file picker and folder picker dialog boxes to allow the user to pick which queries to run. You'll also learn a little about the Scripting Runtime object library and how to work with FileSystemObjects and TextStreams.

Excel VBA Part 56.4 - Return Multiple Result Sets from SQL Queries

Posted by Andrew Gould on 24 June 2019

This video briefly explains how to return multiple sets of results to a single ADO recordset object. You'll learn how to use the NextRecordset method to retrieve the next set of results and continue doing this until all the recordsets have been processed.

Excel VBA Part 56.5 - Basic ADO Commands with SQL Server

Posted by Andrew Gould on 26 June 2019

This video shows you how to use ADO commands to modify data in a SQL Server database. You'll learn how to use the INSERT INTO, UPDATE and DELETE statements with an ADO command object to affect records.

Excel VBA Part 56.6 - ADO Commands and Parameters

Posted by Andrew Gould on 26 June 2019

Learn how to add parameters to an SQL query using ADO and allow users to enter search criteria. This video teaches you how to parameterise a query string and how to pass values to the parameter list using the Array function. You'll also learn how to create ADO parameter objects to make it easy to reuse a parameter multiple times in the same procedure.

Excel VBA Part 56.7 - ADO Commands and Stored Procedures

Posted by Andrew Gould on 05 July 2019

This video shows you how to call and pass values to stored procedures from a SQL Server database using ADO commands. You'll learn how to reference parameters by name, how to pass values to parameters and how to retrieve a result set using a recordset object. You'll also see how to extract information from a stored procedure using output parameters.

Excel VBA Part 58.1 - SQL for Excel Files - Basic Select Statements

Posted by Andrew Gould on 16 May 2021

The first video in our tutorial on writing SQL for Excel files with ADODB shows you how to write basic Select statements to retrieve data from a separate Excel file. You'll learn how to refer to worksheets, cell references and range names and how to deal with worksheets with no column headers. You'll also see how to write a Select list to pick a specific set of columns from the Excel source and how to rename source columns by using aliases in your query. The final part of the video discusses different ways to lay out a longer SQL query, including the controversial topic of where to put your commas!

Excel VBA Part 58.2 - SQL for Excel Files - Sorting Rows in a Query

Posted by Andrew Gould on 16 May 2021

This video explains how to add an Order By clause to sort the results of your SQL queries when selecting data from an Excel file. You'll learn how to sort single and multiple columns by name in both ascending and descending order. You'll also see how to sort a query when the source data has no column headers and how to sort by a column without displaying it in the query results.

Excel VBA Part 58.3 - SQL for Excel Files - Selecting the Top N Rows

Posted by Andrew Gould on 16 May 2021

Learn how to select the top rows from an Excel table an SQL query. You'll learn how to select a specific number of rows from the top of a list and how to use the Order By clause to influence which rows appear at the top. You'll see how to deal with tied rows by including a tie-breaker field and how to select a percentage of the rows from a table.

Excel VBA Part 58.4 - SQL for Excel Files - Selecting All or Distinct Rows

Posted by Andrew Gould on 17 May 2021

In this video you'll learn how to use the All and Distinct keywords in your SQL query to return either every row from a data source or just the rows with unique values. You'll see how to affect the sorting of the query results using the Order By clause and how to use unique row values to provide users with a choice using drop down lists on a user form.

Excel VBA Part 58.5 - SQL for Excel Files - Basic Criteria in Queries

Posted by Andrew Gould on 19 May 2021

This video explains how to add the Where clause to your SQL query to extract only the rows you want from an Excel file. You'll learn about the basics of adding criteria to the Where clause, including how to write basic logical tests and the range of available comparison operators. You'll also learn how to enter numbers, text and date values in your conditions, including the best date format to use. You'll learn how to combine conditions using the And and Or operators, and how to reverse the logic using the Not operator, including how to ensure multiple conditions are evaluated in the correct order by using brackets. You'll see how to use the special Like operator with wildcard characters for working with text columns. You'll also learn how to use the special In and Between operators to shorten longer criteria and make your SQL code more succinct. Finally, you'll learn how to use the Is Null and Is Not Null operators to find or ignore rows with missing values.

Excel VBA Part 58.6 - SQL for Excel Files - Text Criteria and the Like Operator

Posted by Andrew Gould on 23 May 2021

In this video you'll learn how to write criteria for text fields in SQL queries for Excel. You'll learn about the Like operator and the percentage and underscore wildcard characters. You'll see how to use a CharList to search for a range of characters in a string. You'll also learn how to use a variety of string functions such as Len, Left, Right, Mid, UCase, LCase InStr and StrComp to create more complex criteria, including making your string comparisons case-sensitive.

Excel VBA Part 58.7 - SQL for Excel Files - Basic Calculated Columns

Posted by Andrew Gould on 26 May 2021

This video explains the basics of adding calculations to an SQL query for Excel. You'll learn how to add columns to the Select list and assign aliases to them. You'll learn the basic arithmetic operators, what BODMAS and PEDMAS are and how to control the order of evaluation in a complex expression. You'll see how to add calculations to the Where and Order By clauses. You'll learn two different ways to divide numbers and how to use the Mod operator to return the remainder. You'll learn the basics of concatenating values and see what happens when your expressions generate an error. You'll also learn some basic ways to format the results of your expressions using functions such as Round and Format.

Excel VBA Part 58.8 - SQL for Excel Files - Conditional Functions

Posted by Andrew Gould on 01 June 2021

Learn how to use IIf and Switch functions to test conditions and produce different results depending on the answer. You'll see how to create calculated columns using the IIf and Switch functions, as well as how to use the functions to control the sort order of your query results. You'll learn how to use nested IIf and Switch functions and how to write more complex logical tests using the AND, OR, IN and BETWEEN operators.

Excel VBA Part 58.9 - SQL for Excel Files - Nulls in Expressions

Posted by Andrew Gould on 03 June 2021

In this video you'll learn how to work with nulls in your SQL queries. You'll see how to use the IS NULL expression and IsNull function to check for the existence of nulls and how to use the IIf and Switch functions to replace nulls with other values. You'll also see what happens when a null is involved in a calculation and how to make sure that the calculation returns a non-null result.

Excel VBA Part 58.10 - SQL for Excel Files - Date Expressions

Posted by Andrew Gould on 06 June 2021

This video teaches you how to write calculations with dates in your SQL queries for Excel files. You'll learn how to format dates using the FormatDateTime and Format functions, how to extract parts of dates using the Year, Month, MonthName, Day, Weekday, WeekdayName and DatePart functions and how to create a date from other values using the DateSerial and DateValue functions. You'll also learn how to create calculations based on the current date using the Date and Now functions, how to calculate past or future dates using the DateAdd function and how to calculate the difference between dates using the DateDiff function.

Excel VBA Part 58.11 - SQL for Excel Files - Text Expressions

Posted by Andrew Gould on 08 June 2021

Learn how to write expressions to manipulate text in your SQL queries for Excel files. You'll learn how to concatenate text and other values and how to convert the data types of text and numbers using the CStr, CInt and Val functions. You'll see how to use the Replace and Trim functions to clean text values and how to calculate the length of a string with the Len function. You'll also learn how to find a character within a string using the InStr and InStrRev functions and use these in combination with the Left and Mid functions to split a string into multiple parts.

Excel VBA Part 58.12 - SQL for Excel Files - Aggregation Functions

Posted by Andrew Gould on 11 June 2021

This video teaches you how to use aggregation functions in SQL queries for Excel to summarise data in a column. You'll learn how to use the Sum, Count, Min, Max, Avg, StDev and Var functions to generate aggregated values for all the rows in a dataset. You'll also see how to use the results of aggregation functions to create new calculations and how to deal with Nulls in the columns you're aggregating.

Excel VBA Part 58.13 - SQL for Excel Files - Grouping Rows

Posted by Andrew Gould on 11 June 2021

This video explains how to group rows in your SQL queries for Excel files by using the Group By clause. You'll learn how to group data using the values in a column and how to create aggregations using functions such as Sum, Avg and Count. You'll also see how to group data using the results of calculations and how to create multiple row groups.

Excel VBA Part 58.14 - SQL for Excel Files - Criteria in the Having Clause

Posted by Andrew Gould on 11 June 2021

This video shows you how to use the Having clause in an SQL query to apply criteria to the results of aggregation functions. You'll learn why you can't add these criteria to the Where clause, how to add the Having clause to a query and how to write criteria based on aggregation functions such as Sum, Avg and Count. As an added bonus, you'll also learn the importance of the phrase Sweaty Feet Will Give Horrible Odours!

Excel VBA Part 58.15 - SQL for Excel Files - Pivoting Data (Crosstab Queries)

Posted by Andrew Gould on 11 June 2021

This video explains how to create crosstab queries in your SQL queries for Excel files. You'll learn how to prepare the base data using a normal Select statement and how to add the Transform statement and Pivot clause to create column groups and aggregations.

Excel VBA Part 58.16 - SQL for Excel Files - Basic Union Queries

Posted by Andrew Gould on 20 June 2021

This video shows you how to write basic Union queries in SQL for Excel. You'll learn how to merge two or more tables into a single list and the difference between Union and Union All. You'll understand the importance of selecting the same number of columns and how to map columns with different names. You'll also learn how to sort the results of a Union query, how to add calculated columns and how to add criteria.

Excel VBA Part 58.17 - SQL for Excel Files - Union Queries with Total Rows

Posted by Andrew Gould on 22 June 2021

This video explains how to use Union queries to combine detail rows with subtotals and grand totals in a single list.

Excel VBA Part 58.18 - SQL for Excel Files - Union Queries from Multiple Files

Posted by Andrew Gould on 22 June 2021

This video demonstrates how to use the IN clause (not to be confused with the IN operator!) in an ADODB SQL query to select data from multiple files in the same query. You'll learn how to union tables from multiple Excel workbooks, both with and without header rows. As a bonus, you'll also see how to union data from a CSV file.

Excel VBA Part 58.19 - SQL for Excel Files - Inner Joins

Posted by Andrew Gould on 27 June 2021

This video explains how to write inner joins in your SQL queries for Excel files to look up related information in different worksheets. You'll learn a bit about relational database design including what primary keys, foreign keys and one-to-many relationships are. You'll see how to join two tables using an inner join to match rows based on a common value. You'll see an alternative way to do the same thing without using an inner join. You'll also learn the syntax required to include multiple tables in the same query and finally how to apply criteria and sorting to the results.

Excel VBA Part 58.20 - SQL for Excel Files - Outer Joins

Posted by Andrew Gould on 28 June 2021

This video explains how to use left and right outer joins in your SQL queries for Excel files. You'll learn why inner joins don't always return all the results you expect and how to use an outer join to ensure that you always return all of the rows from one of the tables involved in the join. You'll see how to use criteria to find the unmatched rows from a table and find all the invalid entries in a joined table.

Excel VBA Part 58.21 - SQL for Excel Files - Constructing Full Outer Joins

Posted by Andrew Gould on 29 June 2021

This video shows how to simulate a full outer join in your SQL queries for Excel files. You'll learn how to combine the results of a left outer and a right outer join while removing the duplicated rows. You'll see how to use Union and Union All queries to control the duplication of rows in the output. You'll also learn how to combine multiple tables in the same query ensuring that you see every row from every table included.

Excel VBA Part 58.22 - SQL for Excel Files - Join Worksheets from Multiple Files

Posted by Andrew Gould on 04 July 2021

This video explains how to join worksheets from multiple workbooks in a single SQL query. You'll learn about the IN clause and how to reference other workbooks, using a derived table subquery which can be joined to other tables in a query.

Excel VBA Part 58.23 - SQL for Excel Files - Merge Worksheets Side by Side

Posted by Andrew Gould on 04 July 2021

This video shows you how to merge multiple worksheets using a combination of union queries and outer joins. You'll use a union query to create a master list of values to join on and use this query to populate a derived table subquery. You'll join other worksheets to the results of the subquery to produce a side-by-side merged set of results.

Excel VBA Part 58.24 - SQL for Excel Files - Consolidate Worksheets using Derived Tables

Posted by Andrew Gould on 07 July 2021

This video demonstrates how to use derived table subqueries to consolidate multiple worksheets into a single list using the Group By clause and aggregation functions. You'll learn how to nest a Select statement in the From clause of an outer Select statement and how to reference columns from the subquery in the main query. You'll also learn how to apply the Group By clause to the rows returned by the subquery and how to add aggregated values, including grand totals.

Excel VBA Part 58.25 - SQL for Excel Files - Basic Subqueries

Posted by Andrew Gould on 10 July 2021

This video explains how to nest one Select statement inside another Select statement to create a subquery. You'll learn how to use subqueries to return different levels of aggregation in the same Select statement and use this to compare rows against the average value of a dataset and to calculate the contribution of one row to the total. You'll see how to add criteria to a subqery and also how to use a subquery to set criteria for the main query. You'll also learn how to handle multiple rows returned by a subquery using the IN, ALL and ANY keywords

Excel VBA Part 58.26 - SQL for Excel Files - Nested Subqueries

Posted by Andrew Gould on 10 July 2021

This video shows you how to nest multiple levels of query to answer more complicated questions about your data. You'll build on what you learnt in the previous lesson on basic subqueries to nest queries up to four levels (and up to fifty if you really want to!).

Excel VBA Part 58.27 - SQL for Excel Files - Correlated Subqueries

Posted by Andrew Gould on 11 July 2021

This video explains how to use correlated subqueries to create a variety of useful techniques such as ranking values in a column, calculating running totals and creating year to date totals.

Excel VBA Part 58.28 - SQL for Excel Files - Inserting Data into an Existing Sheet

Posted by Andrew Gould on 16 July 2021

This video teaches you how to use INSERT INTO statements in SQL to insert data into an existing Excel worksheet. You'll learn how to insert a single row of specific values, how to insert rows selected from a different worksheet and how to insert rows from a different Excel file. You'll also see how to use outer joins to avoid inserting duplicate rows and how to use union select statements to insert rows from multiple worksheets, ranges and workbooks at the same time.

Excel VBA Part 58.29 - SQL for Excel Files - Selecting Data into a New Sheet or Workbook

Posted by Andrew Gould on 19 July 2021

This video teaches you how to use SELECT INTO statements in SQL to insert data into a new Excel worksheet. You'll learn how to insert a data into a new worksheet in the same workbook as the selected data, how to insert into a new worksheet in a different existing workbook and how to insert data into a new worksheet in a new workbook.

Excel VBA Part 58.30 - SQL for Excel Files - Updating Existing Data

Posted by Andrew Gould on 21 July 2021

This video explains how to update existing data in an Excel workbook using SQL UPDATE statements. You'll learn how to assign a simple value to a column, how to calculate a new value to produce a different result for each row, and how to update information in one worksheet using information stored in a different worksheet. The final part of the video discusses how to import data from a separate workbook using a SELECT INTO statement and then use the imported data to update existing data.

Excel VBA Part 58.31 - SQL for Excel Files - Split a Table into Separate Sheets

Posted by Andrew Gould on 25 July 2021

This video shows you how to split a single Eacel table into multiple worksheets using the values in a column of the table. You'll learn how to create a recordset and populate it with unique values from a column and how to create a SELECT INTO command to create new worksheets. You'll see how to create new worksheets in the same workbook as the source data, in a single different workbook and how to create a separate workbook for each value in the column.

How do I pass Japanese text to a stored procedure parameter in VBA?

Posted by Andrew Gould on 03 April 2021

Learn how to pass Japanese characters from an Excel worksheet into a SQL Server stored procedure using the ADODB library in VBA. You'll learn the basics of character encoding systems, how to use the varchar and nvarchar data types in SQL Server, how to execute stored procedures from VBA and how to use the merge statement in SQL. As a bonus, you'll also learn how to write the characters for dogs, cats, puppies and kittens in Japanese!

How do I get data from a closed Excel file using VBA?

Posted by Andrew Gould on 08 April 2021

One way to get data from a closed Excel workbook is to open it first, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to create a connection object and construct a connection string to talk to the closed Excel file. You'll also see how to create a recordset object and load a set of data into it using an SQL Select statement. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the workbook.

How do I loop through worksheets in a closed workbook with VBA?

Posted by Andrew Gould on 09 April 2021

Looping through the collection of worksheets in a workbook is a standard technique in Excel VBA and easy to accomplish when the workbook is open. Did you know that you can do the same thing when the workbook is closed? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to create a connection object and construct a connection string to talk to the closed Excel file. You'll learn how to query the schema of the workbook to return a list of the worksheets and loop through this list to read the name of each sheet. You'll also learn how to create a recordset object and populate it with data using an SQL Select statement. As a bonus you'll learn about the SQL Union All statement to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.

How do I get data from multiple closed Excel files using VBA?

Posted by Andrew Gould on 12 April 2021

One way to get data from closed Excel workbooks is to open each file before extracting the information, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? This video explains how to do this using the ActiveX Data Objects library. The video shows how to loop through a collection of Excel files in a folder. You'll learn how to create a connection object and construct a connection string to talk to each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using an SQL Select statement. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the closed workbooks.

How do I get data from multiple workbooks with one query in VBA?

Posted by Andrew Gould on 13 April 2021

One way to get data from closed Excel workbooks is to open each file before extracting the information, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? Even better, if the files you're connecting to have the same structure, you can write a single SQL SELECT statement to select the contents of all the files in one go! This video explains how to do this using the ActiveX Data Objects library. The video shows how to loop through a collection of Excel files in a folder. You'll learn how to create a connection object and build a SQL UNION ALL query to get the data from each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using the SQL statement that you've constructed. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the closed workbooks.

How do I loop through worksheets in multiple closed workbooks in VBA?

Posted by Andrew Gould on 14 April 2021

Looping through the collection of worksheets in a workbook is a standard technique in Excel VBA and easy to accomplish when the workbook is open. Did you know that you can do the same thing when the workbook is closed? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to loop through the Excel files in a folder, creating a connection to each file using an ActiveX Data Objects Connection object. You'll learn how to query the schema of each workbook to return a list of the worksheets and loop through this list to read the name of each sheet. You'll also learn how to create a recordset object and populate it with data using an SQL Select statement. As a bonus you'll learn about the SQL Union All statment to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.

How do I populate a listbox using an ADO recordset in VBA?

Posted by Andrew Gould on 21 April 2021

This video shows you how to extract the results of an ADO recordset using the GetRows method and use it to populate a Listbox on a user form in VBA. You'll learn about ActiveX Data Objects connections and recordsets, how to add values to a listbox using both the AddItem method and the List property and how to transpose an array in VBA.

How do I get the column names from an ADO recordset?

Posted by Andrew Gould on 26 April 2021

When you copy data from a recordset into an Excel worksheet you don't get the column headings by default. This video shows you two ways to loop through the Fields collection and write out the Name property of each Field object into the worksheet.

Excel VBA - How do I get data from a CSV file using ActiveX Data Objects

Posted by Andrew Gould on 28 April 2021

Learn how to extract the contents of a CSV file into an Excel workbook without opening the CSV file. You'll learn how to use ActiveX Data Objects connections and recordsets to connect to a closed file and how to write basic SQL queries to return the information from the file.

How do I add a filename to the results of an ADODB recordset?

Posted by Andrew Gould on 30 April 2021

When you're extracting information from multiple files using ADO it can be helpful to include the filename for each row of data. This video shows you how to include a new column in the select list of your query so that you can identify which file a row of data belongs to.

How do I populate an array with an ADODB recordset?

Posted by Andrew Gould on 07 May 2021

This video explains how to use the GetRows method of an ADODB recordset to write data into an array.

How do I count the rows in an ADODB recordset?

Posted by Andrew Gould on 10 May 2021

This video explains how to use the RecordCount property of an ADODB recordset to find out how many rows your query has returned. You'll learn how to set the cursor type and the basic differences between ForwardOnly, Static, Keyset and Dynamic cursors.

How do I find the data type of a column in an ADODB recordset?

Posted by Andrew Gould on 14 May 2021

This video explains how to check the Type property of a field in an ADODB recordset to determine its data type. You'll learn about the ADODB Data Type enumeration and how to translate the code numbers into meaningful descriptions. You'll also learn how to apply specific date or number formatting to a column based on its data type. The video also covers a bug with the CopyFromRecordset method which results in cells on the wrong worksheet being formatted as dates when you insert new worksheets.

How do I refer to a field name with a dot in an ADO SQL query?

Posted by Andrew Gould on 08 August 2021

This video explains what to do if your Excel column name contains a dot and you need to reference it in an SQL query using ADODB. As an added bonus you'll also learn how to deal with exclamation mark characters!

This page has 0 threads Add post