Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / PowerShell

A Quick Guideline for Microsoft Windows PowerShell: Part 3

Rate me:
Please Sign up or sign in to vote.
4.76/5 (21 votes)
21 Aug 2010CPOL3 min read 95.1K   409   47   15
The objective of this article (Part 3) is to introduce you to Microsoft Windows PowerShell and learn some basic command of windows PowerShell.

Table of Contents

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:

***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:

VBScript
$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:

VBScript
$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:

VBScript
$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:

Excell.png

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:

VBScript
$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:

AccessDB.png

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:

VBScript
#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

  • 19th August, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralGood article looking for powershell interview question Pin
logicalweb6-Sep-14 0:17
logicalweb6-Sep-14 0:17 
GeneralRe: Good article looking for powershell interview question Pin
Md. Marufuzzaman6-Sep-14 19:22
professionalMd. Marufuzzaman6-Sep-14 19:22 
GeneralMy vote of 5 Pin
Mohammad A Rahman11-Feb-12 12:18
Mohammad A Rahman11-Feb-12 12:18 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman6-Sep-14 19:23
professionalMd. Marufuzzaman6-Sep-14 19:23 
GeneralIntegrating the word script with the sql server script Pin
sqlDude17019-Feb-11 5:16
sqlDude17019-Feb-11 5:16 
GeneralRe: Integrating the word script with the sql server script Pin
Md. Marufuzzaman19-Feb-11 17:22
professionalMd. Marufuzzaman19-Feb-11 17:22 
GeneralMy vote of 5 Pin
Mohsiul Haque2-Nov-10 2:46
Mohsiul Haque2-Nov-10 2:46 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman6-Sep-14 19:23
professionalMd. Marufuzzaman6-Sep-14 19:23 
GeneralMy vote of 5 Pin
Tanvir Hasan Turan19-Sep-10 23:25
Tanvir Hasan Turan19-Sep-10 23:25 
Nice post.
GeneralExcellent Pin
Taslima Bagum22-Aug-10 5:12
Taslima Bagum22-Aug-10 5:12 
GeneralRe: Excellent Pin
Md. Marufuzzaman22-Aug-10 18:04
professionalMd. Marufuzzaman22-Aug-10 18:04 
GeneralMy vote of 5 Pin
addy200520-Aug-10 4:43
addy200520-Aug-10 4:43 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman20-Aug-10 7:09
professionalMd. Marufuzzaman20-Aug-10 7:09 
GeneralGreat! Pin
addy200519-Aug-10 2:07
addy200519-Aug-10 2:07 
GeneralRe: Great! Pin
Md. Marufuzzaman19-Aug-10 4:25
professionalMd. Marufuzzaman19-Aug-10 4:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.