Table of Contents
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:
***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.
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
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.
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 =
$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()
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:
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:
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
I hope that this might be helpful to you. Enjoy!
- Microsoft Development Network
- 19th August, 2010: Initial post