A Quick Guideline for Microsoft Windows PowerShell: Part 3

• Download script - 29.65 KB

Table of Contents

• Introduction
• How to work with objects
• Microsoft Windows PowerShell & Microsoft Office System
  • PowerShell Script for Microsoft Word
  • PowerShell Script for Microsoft Excel
  • PowerShell Script for Microsoft Access
• Microsoft Windows PowerShell & Microsoft SQL Server
• Conclusion
• Reference
• History

Introduction

Microsoft Windows PowerShell allow us to work with the Microsoft Office System APIs. For example, Microsoft Office 12.0 Object Library. Before I start, I would like to request you to read:

• A Quick guideline for Microsoft Windows PowerShell: Part 1
• A Quick guideline for Microsoft Windows PowerShell: Part 2

***If you read part-1 and part-2 from the above links, then I think it will help you to understand the current scenario, what we are actually doing in this section.

However, the objective of this article (Part 3) is to introduce you to Microsoft Windows PowerShell and learn some basic commands of windows PowerShell. We will also try to work with various objects; we will work with Microsoft Office System, XML document, Microsoft SQL server and some other stuff as well.

How to Work with Objects

Every action you take in PowerShell is done within the context of object. We can create an instance of a COM object. To create an instance of a COM object, we use the ComObject parameter and specify the ProgID of the object as its value.

Syntax

New-Object [-typeName] <string> [[-argumentList] <[Object]>] [<CommonParameters>]
New-Object [-comObject] <string> [-strict] [<CommonParameters>]

For example, we want to write a PowerShell script which will open the browser and navigate to a custom / users defined URL (http://www.codeproject.com/). So, how can we achieve this? For doing that, our script will be like:

$objBrowser = New-Object -comobject InternetExplorer.Application 
$objBrowser.navigate2("http://www.codeproject.com/")
$objBrowser.visible = $true

Microsoft Windows PowerShell & Microsoft Office System

Well, I hope that you dig up some basics of windows PowerShell. So let’s start...
In this section, we will learn how to create an instance of an Object. We will write some scripts for Microsoft Office System & work with some other APIs as well.

PowerShell Script for Microsoft Word

Now let’s try to have some fun, our requirement is to read the content from a Microsoft Office Word document file, manipulate the content of that document and finally save the document. For doing this; the Windows PowerShell script will be like:

$objWord = New-Object -Com Word.Application
$objWord.Visible = $true
$objMissingValue = [System.Reflection.Missing]::Value

$objDocument = $objWord.Documents.Add($objMissingValue, _
	$objMissingValue, $objMissingValue, $objMissingValue)
$objParaHeader = $objDocument.Paragraphs.Add($objMissingValue)
$objParaHeader.Range.Style = "Heading 1"
$objParaHeader.Range.Text = "The power of Microsoft Windows PowerShell"
$objParaHeader.Range.InsertParagraphAfter()

$objParaText = $objDocument.Paragraphs.Add($objMissingValue)
$objParaText.Range.Text = "I will not say I have failed 1000 times; _
   I will say that I have discovered 1000 ways that can cause failure – Thomas Edison."
$objParaText.Range.InsertParagraphAfter()

$filename = 'C:\Script\PowerShell-Example.doc'
$objDocument.SaveAs($filename,
$objMissingValue, $objMissingValue,
$objMissingValue, $objMissingValue,
$objMissingValue, $objMissingValue,
$objMissingValue, $objMissingValue,
$objMissingValue, $objMissingValue)

##Once the script has added all of the required content the document should be closed:
$objDocument.Close()
$objWord.Quit()

PowerShell Script for Microsoft Excel

Now let’s try to have some fun with Microsoft Excel, our requirement is to read the content from a Microsoft Office Excel spreadsheet and display the content as well. For doing this; the Windows PowerShell script will be like:

$objOleDbConnection = New-Object "System.Data.OleDb.OleDbConnection"
$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$objDataTable = New-Object "System.Data.DataTable"

$objOleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;_
 Data Source=C:\Script\PowerShell-Example.xls;Extended Properties=""Excel 8.0;HDR=YES"""
$objOleDbConnection.Open()

$objOleDbConnection.State

$objOleDbCommand.Connection = $objOleDbConnection
$objOleDbCommand.CommandText = "SELECT * FROM [Sheet1$]"

##set the Adapter object
$objOleDbAdapter.SelectCommand = $objOleDbCommand

##fill the objDataTable object with the results
$objOleDbAdapter.Fill($objDataTable)

##To display the "raw" contents, just enter
$objDataTable

##show the first line 
$objDataTable.Rows[0].Name

## close the connection 
$objOleDbConnection.Close() 

The figure below is the output of the above script:

PowerShell Script for Microsoft Access

For now, our requirement is to read the content of a table from a Microsoft Office Access database and display the content as well. For doing this; the Windows PowerShell script will be like:

$objOleDbConnection = New-Object "System.Data.OleDb.OleDbConnection"
$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$objDataTable = New-Object "System.Data.DataTable"

$objOleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;_
	Data Source=C:\Script\PowerShell-Example.mdb;"
$objOleDbConnection.Open()

$objOleDbConnection.State

$objOleDbCommand.Connection = $objOleDbConnection
$objOleDbCommand.CommandText = "SELECT * FROM [Example]"

##set the Adapter object
$objOleDbAdapter.SelectCommand = $objOleDbCommand

##fill the objDataTable object with the results
$objOleDbAdapter.Fill($objDataTable)

##To display the "raw" contents, just enter
$objDataTable

## close the connection 
$objOleDbConnection.Close() 

The figure below is the output of the above script:

Microsoft Windows PowerShell & Microsoft SQL Server

We can use PowerShell script for several purposes. For now, our requirement is to read the contents of a table from a Microsoft SQL Server database, display the contents and finally export the data into a CSV file as well. For doing this; the Windows PowerShell script will be like:

#Connection Strings
$Database = "Northwind"
$Server = "localhost"
#Export File
$fileName = "C:\Script\SQLData.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT TOP 100 *  FROM [Northwind].[dbo].[Orders]"
$objSqlConnection = New-Object System.Data.SqlClient.SqlConnection
$objSqlConnection.ConnectionString = "Data Source=$Server;_
	Initial Catalog=$Database;Integrated Security = True"
$objSqlCommand = New-Object System.Data.SqlClient.SqlCommand
$objSqlCommand.CommandText = $SqlQuery
$objSqlCommand.Connection = $objSqlConnection
$objSqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$objSqlAdapter.SelectCommand = $objSqlCommand
$DataSet = New-Object System.Data.DataSet
$objSqlAdapter.Fill($DataSet)
#Populate Hash Table
$objTable = $DataSet.Tables[0]
$objTable | Export-CSV $fileName

$objTable 

Conclusion

I hope that this might be helpful to you. Enjoy!

Reference

• Microsoft Development Network

History

• 19^th August, 2010: Initial post