Lecture Description
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
By Andrew Gould
You can download the files needed for the video here:
www.wiseowl.co.uk/videos/excelvba/ado-commands.htm
www.wiseowl.co.uk - The ADO Command object essentially allows you to write SQL statements directly into your VBA code and execute them on whichever database you've connected to - powerful stuff! This video focusses on the INSERT, UPDATE and DELETE statements to allow you to modify data based on information contained in your Excel workbooks. We'll cover how to build dynamic SQL statements and how to avoid the main danger of dynamic SQL. You'll also get an idea of how transactions work in databases, allowing you to revert your data back to a previous state if things go wrong with your code!
Course Index
- Getting Started in the VB Editor
- Writing Your First VBA Macro
- What to do When Things Go Wrong (Errors and Debugging)
- Buttons, Toolbars and Keyboard Shortcuts
- Selecting Cells (Range, Cells, Activecell, End, Offset)
- Worksheets, Charts and Sheets
- Workbooks
- Variables
- Object Variables
- Message Boxes
- Input Boxes
- Application.InputBox
- With Statements
- If Statements
- Select Case Statements
- Do Until and Do While Loops
- Find and FindNext
- For Next Loops
- For Each Loops
- Creating Functions
- Error Handling (On Error, Resume, GoTo)
- Event Procedures
- Application Events
- User Forms
- Files and Folders (FileSystemObjects)
- Text Files (FileSystemObjects)
- File Dialogs
- Arrays
- Constants and Enumerations (Const, Enum)
- Creating Word Documents
- Creating and Updating Linked Word Tables
- Creating PowerPoint Presentations
- Creating Outlook Emails
- ADO (ActiveX Data Objects) Querying a Database
- ADO (ActiveX Data Objects) Modifying Data with Recordsets
- ADO (ActiveX Data Objects) SQL Statements (INSERT, UPDATE, DELETE)
- Creating Add Ins
- Type Declarations (UserDefined Types)
- Class Modules
- Using Class Modules in Other Projects
- Custom Collections
- Typed Collections
- Dictionaries
- Disabling Screen Updates
- Saving as a PDF
- Data Labels in Scatter Charts
- ByRef and ByVal
- ByRef and ByVal (When to use Parentheses)
- Making Excel Talk
- Finding the Last Used Row, Column and Cell
- Querying Web Pages using Query Tables
- Browsing to Websites and Scraping a Web Page
- Web Scraping Across Multiple Pages
- Downloading Files from Websites
- Introduction to Charts in VBA
- Embedding Charts in Worksheets
- Introduction to Pivot Tables in VBA
- Pivot Tables using an Access Database
- Pivot Tables using a SQL Server Database
- Pivot Tables and Consolidation Ranges
- PowerPivot Data Models
- Pivot Charts
- Pivot Table Slicers
- Pivot Table Date Fields and Timelines
- Introduction to Shapes
- Formatting Shapes
- Adding Text to Shapes
- Working with Strings
- Splitting Strings
- The Join Function and Concatenating Strings
- Replacing Strings
- Converting the Case of Text
- The Asc and Chr Functions
- The IIf Function
- The Switch Function
- Working with Dates
Course Description
Excel VBA is a powerful (and fun!) tool which allows you to automate your Excel workbooks. These videos will help you through your first steps on the way to becoming a programmer in Excel. You don't need any prior programming knowledge as we'll take things from first principles and hopefully give you a flavour of the cool things you can do with a little bit of effort!