Microsoft training videos created by Andrew Gould of Wise Owl (page 1 of 11)

Excel VBA Part 29.2 - Outlook Email Events

Posted by Andrew Gould on 29 August 2017

This video explains how to use the WithEvents keyword to access the Send and Close events of an Outlook MailItem. You'll learn how to use a basic class module to enable access to the events of an object, as well as how to create basic event handlers.

Report Builder 2016 Pt 4.1 - Embedded Data Sources and Datasets

Posted by Andrew Gould on 08 May 2017

You can't do much in Report Builder without some data to work with. This video explains how to get data from a SQL Server database using embedded data sources and datasets. You'll learn how to use the Query Designer to build a query, including how to choose which columns are included and apply filters to the rows. You'll also see how the Query Designer automatically detects relationships between database tables and what to do if this doesn't work!

Excel VBA Part 55.1 - Working with Dates

Posted by Andrew Gould on 03 May 2017

This video explains the basics of working with dates in VBA. You'll learn about the Date data type and how to write unambiguous dates in your VBA code. You'll also learn about Excel's leap year bug and why some VBA dates don't match those in Excel. You'll see how to return the current date and time, as well as various techniques to format dates. The final part of the video describes a variety of date calculations and functions including the difference between DateDiff and DateDif, calculating working days using NetWorkDays and how to calculate age in years accurately.

SSRS 2016 Pt 2.3 - Datasets Using Views

Posted by Andrew Gould on 03 May 2017

A SQL Server View provides a convenient way to populate a dataset in your SSRS reports. This video teaches you how to create and modify views and how to use them with both shared and embedded datasets.

Excel VBA Part 53.6 - The Asc and Chr Functions

Posted by Andrew Gould on 06 March 2017

This video explains how to use the Asc and Chr functions in VBA.

SSRS 2016 Pt 2.2 - Shared and Embedded Datasets

Posted by Andrew Gould on 06 March 2017

Datasets provide your reports with the data that you want to display. This video explains the basics of creating both shared and embedded datasets and producing simple reports which use them. You'll learn how to write basic SQL Select statements, how to use the query designer and how to upload the finished reports to a report server.

SSRS 2016 Pt 2.1 - Shared and Embedded Data Sources

Posted by Andrew Gould on 06 March 2017

You won't get far in SSRS unless you know how to create a data source to connect to the data you want to present! This video explains how to create both shared and embedded data sources, how to use them in your reports and how to deploy them to a report server.

Excel VBA Part 54.2 - The Switch Function

Posted by Andrew Gould on 27 February 2017

The Switch function allows you to perform multiple logical tests in a single, succinct expression in VBA. For simple conditions, you can use Switch as an elegant replacement for long-winded Select Case statements. This video shows how the Switch function works, inluding how to use it to create a user-defined function to help separate a list of data into different sheets.

Excel VBA Part 54.1 - The Iif Function

Posted by Andrew Gould on 27 February 2017

The IIF function in VBA is, apart from the spelling, exactly like the If function used in Excel worksheets. This video provides a quick overview of how the function works, comparing it with the If statement and creating a practical example to separate a list of data into different worksheets.

Excel VBA Part 53.5 - Converting the Case of Text

Posted by Andrew Gould on 27 February 2017

This video explains how to change the case of text using VBA. You'll see how to make string comparisons case insensitive using the Option Compare statement as well as the UCase, LCase and StrComp functions. You'll learn how to convert strings to Proper Case or Title Case. The video also explains how to create custom functions for converting text into Sentence Case and to toggle the case of individual characters in a string.

Excel VBA Part 53.4 - Replacing Strings

Posted by Andrew Gould on 27 February 2017

In this video you'll learn how to use the Replace function to substitute characters within a string. As a practical example, we'll look at how to generate file names from cell contents and replace the illegal characters.

Excel VBA Part 53.3 - The Join Function and Concatenating Strings

Posted by Andrew Gould on 22 February 2017

This video explains how to join or concatenate strings in VBA. You'll learn about to different operators you can use to do this, and why one is better than the other. You'll also learn how to use the Join function to take the contents of an array and concatenate them into a single string. The final part of the video shows a couple of practical uses for these techniques with an example that writes data to text files.

Excel VBA Part 53.2 - Splitting Strings

Posted by Andrew Gould on 21 February 2017

This video explains how to use VBA functions to split strings into their constituent parts. You'll learn about the Left, Right and Mid functions for extracting parts of a string, how to identify the position of one string within another using the InStr and InstrRev functions and how to use the Split function to break a single string into multiple parts and store them in an array.

Excel VBA Part 53.1 - Working with Strings

Posted by Andrew Gould on 20 February 2017

This video explains the basics on working with text, or strings, in VBA. You'll learn about the String data type, how to use the $ type-declaration character and how to create variable and fixed-length strings. You'll see how to concatenate multiple values into a single string, including some string constants for special characters such as tab spaces and new lines - ever wondered why there are so many ways to create a new line in VBA? This video explains why! The final part of the video talks about comparing strings, including how to deal with case-sensitive comparisons and how to use wildcard characters to match paterns of text.

Report Builder 2016 Pt 3 - Saving to a Report Server

Posted by Andrew Gould on 20 February 2017

To allow others to view your Report Builder reports, you'll need to deploy them to a report server - this video explains how to do precisely that. You'll learn where to find your report server URL, how to connect Report Builder to a report server, how to deploy a report and how to view it in a web browser using the Reporting Services Web Portal application. You'll also learn a little about managing deployed reports, including how to download the report definition file, how to make changes to reports and re-upload them, and how to organise them on the report server using folders.

Report Builder 2016 Pt 2 - Creating Your First Report

Posted by Andrew Gould on 20 February 2017

This video shows you the basics of creating a report in Report Builder. You'll start with a blank report and learn how to create a data source to connect to a database, followed by a dataset to retrieve the results of a query. Next, you'll learn how to add a table to display the results, including how to assign dataset fields to table columns, how to sort and filter the table, along with how to use a variety of formatting options. The final part of the video explains how to save, close and reopen report files.

Excel VBA Part 52.3 - Adding Text to Shapes

Posted by Andrew Gould on 16 February 2017

You can add text to most shapes that you can draw in Excel. This video explains the basics of working with text in shapes, including how to add to text to a basic shape, how to format text inside shapes and how to change the text alignment and orientation.

SSRS 2016 - Comparing Report Builder with SQL Server Data Tools

Posted by Andrew Gould on 15 February 2017

If you're confused by whether to use Report Builder or SQL Server Data Tools to create your Reporting Services reports, this video might help you to work out which product to choose. You'll see a side-by-side comparison of creating a report from the very beginning, all the way through to deploying it to a report server using both pieces of software. Along the way we'll point out the major differences between the two applications, along with the pros and cons of each.

Excel VBA Part 52.2 - Formatting Shapes

Posted by Andrew Gould on 14 February 2017

The previous video in the series explained how to draw shapes on a worksheet - this one explains how to make them look more interesting by applying a variety of formatting effects to them.

Report Builder 2016 Pt 1 - Installing Report Builder 2016

Posted by Andrew Gould on 07 February 2017

This video takes you through the basics of installing Report Builder 2016, including a short introduction to what Report Builder is designed to do!