Click here to Skip to main content
Click here to Skip to main content
Go to top

A Quick Guideline for Microsoft Windows PowerShell: Part 3

, 21 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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:

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

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:

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

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

Share

About the Author

Md. Marufuzzaman
CEO
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.
 
Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.
 
An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.
 
Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.
 
Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).
 
Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:
 
1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.
Follow on   Twitter   LinkedIn

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMohsiul Haque2-Nov-10 2:46 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman6-Sep-14 19:23 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 21 Aug 2010
Article Copyright 2010 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid