Home > Scripting > Powershell : Generate passwords into an excel file

Powershell : Generate passwords into an excel file


Some months ago a posted a VBS script that generated passwords into an excel file (found here). This time I wrote one that can do this by using powershell. This scipt can be used with other powershell scripts I wrote in combination with excel. Very handy if you need to create passwords for hunderds of new student accounts.

####################################################################
#
PowerShell Script to generate a password into an excel file #
#
#
#
Version: 0.1 #
#
#
#
Requirements: #
#
1. Powershell #
#
3. Excel 2007 Installed #
#
4. Excel file with the following Columns and data: #
#
Column 1 (A) : Last Name #
#
Column 2 (B) : Middle Name (like van, de, or blank) #
#
Column 3 (C) : First Name #
#
Column 4 (D) : Login Name (like 22222) #
#
Column 5 (E) : Class (like 4K1) #
#
Column 6 (F) : Sector (VMBO, HVWO, PRO) #
#
Column 7 (G) : Password #
#
#
#
Changelog: #
#
0.1 First version Richard #
#
#
#
###################################################################

$strExcelFile = "C:\Scripts\students.xlsx"

####################################################################
#
FUNCTIONS #
#
###################################################################
function Release-Ref ($ref) {
([
System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[
System.GC]::Collect()
[
System.GC]::WaitForPendingFinalizers()
}
function CreatePassword([int]$length){
$specialCharacters = "_!+"
$lowerCase = "abcdefghijklmnopqrstuvwxyz"
$upperCase = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
$numbers = "1234567890"
$res = ""
$rnd = New-Object System.Random

do
{
$flag = $rnd.Next(4);
if ($flag -eq 0)
{
$res += $specialCharacters[$rnd.Next($specialCharacters.Length)];}
elseif ($flag -eq 1)
{
$res += $lowerCase[$rnd.Next($lowerCase.Length)];}
elseif ($flag -eq 2)
{
$res += $upperCase[$rnd.Next($upperCase.Length)];}
else
{
$res += $numbers[$rnd.Next($numbers.Length)];}
}
while (0 -lt $length--)
return $res
}
####################################################################
#
MAIN SCRIPT #
#
###################################################################
#
Get First Cell and last Cell value (using VBS for this)
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
$strFirstCell = [Microsoft.VisualBasic.Interaction]::InputBox("First Cell value (like G2)", "Password Generator for Excel")
$strLastCell = [Microsoft.VisualBasic.Interaction]::InputBox("Last Cell value (Like G100)", "Password Generator for Excel")
$objCellRange = $strFirstCell + ":" + $strLastCell

#Open COM object, start Excel, open Excel File with workbook
$objExcel = New-Object -Comobject Excel.Application
$objExcel.Visible = $True
$objExcel.DisplayAlerts = $False
$objExcelWorkbook = $objExcel.Workbooks.Open($strExcelFile)

#loop through CellRange and create password with 6 chars (change 5 to something else if more required)
ForEach ($objExcelCell In $objExcel.Range($objCellRange)) {$objExcelCell.Value() = createpassword 5}

#Save and close Excel Workbook, close Excel, cleanup and kill EXCEL32 process
$objExcelWorkbook.SaveAs($strExcelFile)
$objExcel.DisplayAlerts = $True
$objExcelWorkbook.Close()
$objExcel.Quit()
Start-Sleep -Seconds 2
Release-Ref $objExcelWorkbook
Release-Ref $objExcel
spps -n excel
####################################################################
#
END MAIN SCRIPT #
#
###################################################################

Feel free to comment.

About these ads
  1. Peter
    November 24, 2011 at 2:21 pm

    Thanks for the code it saved me a lot of time!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: