Automate Like a Pro: PowerShell’s Excel COM Object Magic

This video shows you a deep dive into Excel com objects using PowerShell
Creating workbook
Creating Sheets
Adding data from Active Directory
Creating tables
Doing vlookups
and MUCH MUCH More!!!

00:00 Intro
00:55 Create Excel Com Object
01:02 Make Excel Visible
01:16 Create Excel Workbook
01:31 Select Worksheet
01:52 Add new worksheet
02:20 Update worksheet 2 name
02:35 Update worksheet 1 name
02:45 Active Sheet (visually change sheet selected)
03:11 Active Directory query for disabled users
03:34 Column Headers being added
04:20 Foreach adding data to cells
05:43 Autofit (Autosize) Columns
06:15 Find last row in table
06:58 Create a Table
07:50 Selecting Table Styles
09:25 Select Another tabe (activate)
09:35 Add users to do vlookup() on
09:45 Select the last row
10:03 Explain for loop adding vlookup() to column B
12:20 Create Table
12:30 Autofit (Autosize) Columns
12:38 Save Workbook to file
12:49 Quit Excel
13:07 Open existing workbook from file
13:36 Select worksheet
13:39 Show name of worksheet
14:10 Switch Sheet (Activate)
14:12 Read value from Cell
14:27 Change value in Cell
14:29 Delete Row
15:10 Delete Column
15:24 Search worksheet for value
15:55 Change value of cell we searched for
16:12 Talking about autofill of cells
17:09 Doing autofill

[gitsideways]

Looked all over Google for information on the Excel com object usage documentation before stumbling upon this video. It has answered all the questions I had and didn't know I needed. Thanks so much PowerShellNerd!

[tfofurn]

When you don't want a statement to emit output, you can pipe to Out-Null so the output isn't lingering in a variable.

[plee]

Fantastic video! Thank you for executing each line with Excel side-by-side. Really helps exemplify what exactly the methods do in each. Concatenation is really crucial to loops. Does this COM object work with CSV also?

[MethodOverRide]

Nice video! Do you have a github repo?

[HEADSPACEnTIMING]

Glad I found this! Thank you for posting!