Microsoft training videos created in 2016 by Wise Owl (page 1 of 2)

Showing videos 1-20 (out of 38)

Excel VBA Part 51a - Introduction to Pivot Tables in VBA

Posted by Andrew Gould on 22 November 2016

A pivot table is a fantastic tool for summarising a large quantity of data. This video shows you the basics of creating pivot tables using VBA, starting with the creation of a pivot cache. You'll also see how to create new pivot tables, how to manipulate pivot fields and pivot items and how to apply filters to the pivot table.

Excel VBA Part 49 - Downloading Files from Websites

Posted by Andrew Gould on 21 November 2016

Excel VBA doesn't have a native method for downloading files from websites but you can declare an API function that will enable you to do this. This video takes you through the process of declaring the API function and using it in your code, along with a bunch of other useful techniques such as using folder pickers, creating folders with FileSystemObjects and opening a Windows Explorer window using the Shell function.

Excel VBA Part 50b - Embedding Charts in Worksheets

Posted by Andrew Gould on 21 November 2016

ChartObjects are charts that are embedded in a sheet in an Excel workbook. This video teaches you about the basic differences between charts and chartobjects, as well as how to change one type into another. You'll also learn a couple of neat tricks for positioning chartobjects on a sheet, including how to arrange multiple chartobjects so that they don't overlap.

Excel VBA Part 50a - Introduction to Charts in VBA

Posted by Andrew Gould on 17 November 2016

This video helps you to get started with charts in Excel VBA. You'll learn how to create a new chart sheet and set the chart's data source using a variety of techniques, including how to dynamically set the data source based on conditions. You'll also see lots of ways to change the appearance of the chart, including using chart layouts, chart colour schemes, chart styles and even how to create your own custom chart template.

Excel VBA Part 48 - Scraping Multiple Web Pages

Posted by Andrew Gould on 17 November 2016

What's better than scraping one web page? Scraping lots of them with the same procedure, of couse! This video explains how to loop over multiple pages using Microsoft's HTML and XML object libraries. You'll learn about HTML tags and classes, the Document Object Model and how to loop over elements on a page.

Excel VBA Part 47 - Browsing to Websites and Scraping Web Page

Posted by Andrew Gould on 14 November 2016

Scraping a web page in VBA involves getting a reference to an HTML document and then processing its elements to extract the useful parts and write them to another location in a readable format. This video shows you how to do that using both Internet Explorer and basic XML HTTP requests. You'll learn a bit about the Document Object Model, as well as how to identify HTML elements by name and by tag type. You'll also see how to loop through various collections of HTML elements and their children using an example involving exchange rate tables.

Excel VBA Part 46 - Querying Web Pages with Query Tables

Posted by Andrew Gould on 14 November 2016

Querying web pages in Excel VBA is simple when you use Query Tables. This video shows you how to create a query table linked to a web page, how to choose which tables from the web page to return, and how to pass values to a URL query string to create dynamic, updatable web queries in your Excel workbooks.

SQL Server 2016 Pt 7 - Computed Columns

Posted by Andrew Gould on 10 November 2016

A computed column in a SQL Server table allows you to store an expression which calculates a new value based on the data in other columns of the same table. This video explains how to create computed columns and how to persist the result of the calculation so that it's stored in the table. You'll also learn a little about SQL Server's expression syntax, including how to use some of its built-in functions, the difference between deterministic and non-deterministic functions, how to use Case expressions to test conditions and how to concatenate strings. We'll do this by building a few example computed columns including constructing a full name from first and last name columns, calculating age in days using the DateDiff function and calculating age in years using DateDiff, DateAdd and a Case expression.

SQL Server 2016 Pt 6 - Designing and Creating Relational Databases

Posted by Andrew Gould on 10 November 2016

Creating the optimal design for relational database can be a long and complex process - this video takes you through the key steps of normalising data including identifying repeating values, creating separate tables to store those values, and creating primary keys, foreign keys and relationships. The video also includes a section drawing database diagrams, which is the most fun you can have when designing a database!

SQL Server 2016 Pt 5 - Basic Table Design, Data Types and Constraints

Posted by Andrew Gould on 10 November 2016

Designing basic tables in SQL Server involves deciding which columns the table should include, what the data type of those columns should be (not always an easy choice!) and a few additional properties such as whether the column can be left empty (or NULL) and whether there should be any constraints on the values entered into the column. This video takes you through that process to design and create a simple table to store information about people. The video contains a discussion of the main text, number and date data types in SQL Server - you'll learn the difference between char, varchar, nchar and nvarchar data types, as well as the difference between exact and approximate numerics. The video also covers the basics of inserting and editing data before the final part shows how to automatically generate scripts that will recreate the table and its precious data in the event that you delete it.

Excel VBA Part 27b - Creating and Updating Linked Word Tables

Posted by Andrew Gould on 07 November 2016

Using Excel VBA to create a linked table in a Word document is pretty straightforward. Where things get tricky is when you move or rename the original Excel file. This video explains how to create the original linked table followed by how to update all of the links in a Word document by looping over its Fields collection. The end of the video also shows how to attach the code to the save events of the workbook so that you don't even have to remember to run it yourself!

SQL Server 2016 Pt 4 - Databases, Logins, Users, Roles and Schemas

Posted by Andrew Gould on 02 November 2016

Creating a new SQL Server database is pretty simple, as the first part of this video shows. What's a little more complex is configuring access to that database for other users. This video explains the basics of logins, users, server and database roles, permissions and schemas to provide you with an overview of what's possible.

SQL Server 2016 Pt 3 - System Databases, Backing Up and Restoring

Posted by Andrew Gould on 01 November 2016

SQL Server creates several important system databases when you install a new instance. This video explains what the master, model, msdb and tempdb databases are for along with a couple of the interesting things you can do with them. You'll also see how you can easily create a backup of the system databases and, somewhat more awkwardly, how to restore those backups in the event that something goes wrong.

SQL Server 2016 Pt 2 - Using Configuration Manager and Management Studio

Posted by Andrew Gould on 31 October 2016

SQL Server Configuration Manager allows you to control the various services that make up your SQL Server installation. SQL Server Management Studio is the tool you can use to connect to a server and begin working with the databases installed on it. This video takes you through the basics of using both tools to get ready for building databases in the next part of this tutorial.

SQL Server 2016 Pt 1 - Installing SQL Server 2016 Developer Edition

Posted by Andrew Gould on 31 October 2016

SQL Server 2016 Developer Edition is free as part of Microsoft's Visual Studio Dev Essentials program. This video shows you how to obtain your free copy and install it in a Windows 10 environment. You won't learn how to become a SQL Server Administrator here but you will see the basics of getting up and running with SQL Server 2016.

Excel VBA Forms Pt 19 - The RefEdit Control

Posted by Andrew Gould on 31 October 2016

The RefEdit control provides you with a way to select cells from the comfort of a user form in Excel. This video explains how to make the RefEdit control available, how to use it to select cells and how to use the value it returns to manipulate the cells you've selected. The video also inculdes a section on a few potential issues with the RefEdit control and shows a couple of alternative techniques using the Application.InputBox method.

Excel VBA Forms Pt 18 - Installing the Date Time Picker and Month View Controls

Posted by Andrew Gould on 26 October 2016

Entering dates into a text box is awkward, difficult to validate and dull. Selecting dates from a dedicated Date Time Picker or Calendar solves all of these problems at once! This video explains how to install and register the Microsoft Date and Time Picker Control and the Microsoft Month View Control and explains how to add them to your User Form Control Toolbox. The video also briefly explains how to use these controls in your forms.

Excel VBA Part 45 - Finding the Last Used Row, Column and Cell

Posted by Andrew Gould on 25 October 2016

There are several techniques you can use in VBA to find the last row, column or cell in a worksheet. This video shows you a range of options including using the End and Offset properties; the CurrentRegion property of a Range and the UsedRange property of a Worksheet; the LastCell option of the SpecialCells method and, finally, the trusty Find method.

Excel VBA Part 43b - ByRef and ByVal (When to use Parentheses)

Posted by Andrew Gould on 24 October 2016

When to use parentheses (round brackets) is one of the apparently annoyingly inconsistent features of VBA. There are some clearly defined rules which dictate when you should and shouldn't use parentheses and this video attempts to explain them!

Excel VBA Part 44 - Making Excel Talk

Posted by Andrew Gould on 17 October 2016

One of Excel's more unusual features is its ability to speak to you! Not intelligently, of course, but you can make Excel vocalise a specific phrase or the contents of cells, or any other expression you can think of. This video shows you how to access the speech feature in Excel and use it to create a couple of small but fun examples.

This page has 0 threads Add post