Training videos for learning VBA - WORKING WITH DATA

We have 38 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.

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.

This page has 0 threads Add post