Category Archives: Scripting & programming

Somthing about Powershell, VB Script, C#, VB.NET

Powershell: Check for Administrator rights

Hi,

for a lot of powershell scripts Administrator permissions are necessary. In powershell, you can check if you are elevated by the following script code.
Insert the snippet at the top of your script. It checks the credentials and exits the script if the administrator permissions are missing.
Continue reading Powershell: Check for Administrator rights

Powershell: Some basic XML handling with Powershell and .NET

Hi everybody,

here are some fundamentals to handle XML Files with powershell.

The following commands uses these XML File


<config description="Config file for testing">
    <system description="Document Management">
        <document authorFirstName="Kirk" authorSurname="Author1" date="20130503 01:15:43" description="Document Hammet" index="3">C:\temp\doc0001.txt</document>
        <document authorFirstName="Lars" authorSurname="Author5" date="20130503 10:05:42" description="Document Ulrich" index="4">C:\temp\doc0002.txt</document>
        <document authorFirstName="Cliff" authorSurname="Author5" date="20130612 11:54:33" description="Document Burton"  index="6">C:\temp\doc0051.txt</document>
        <document authorFirstName="Jason" authorSurname="Author2" date="20130806 04:02:41" description="Document Newsted"  index="1">C:\temp\doc0041.txt</document>
        <document authorFirstName="Robert" authorSurname="Smith" date="20131202 07:12:03" description="Document Trujillo"  index="2">C:\temp\doc0012.txt</document>
        <document authorFirstName="James" authorSurname="Smith" date="20130211 09:05:59" description="Document Hetfield"  index="5">C:\temp\doc0003.txt</document>
    </system>
</config>

Creating  a XML File, create a document root, create subelements, add and set attributes, save the file

# Create a new XML File with config root node
[System.XML.XMLDocument]$oXMLDocument=New-Object System.XML.XMLDocument
# New Node
[System.XML.XMLElement]$oXMLRoot=$oXMLDocument.CreateElement("config")
# Append as child to an existing node
$oXMLDocument.appendChild($oXMLRoot)
# Add a Attribute
$oXMLRoot.SetAttribute("description","Config file for testing")
[System.XML.XMLElement]$oXMLSystem=$oXMLRoot.appendChild($oXMLDocument.CreateElement("system"))
$oXMLSystem.SetAttribute("description","Document Management")
# Save File
$oXMLDocument.Save("c:\temp\config.xml")

Loading an existing XML File, read an element, do some XPATH queries

Continue reading Powershell: Some basic XML handling with Powershell and .NET

Windows: Create a new local User Account and add them to a local group by using .NET and C#

Hi,

a short post. It describes how to create a new local Windows User account and add them to an existing local user group.

First of all, include the following references in your project:


using System.DirectoryServices;
using System.Runtime.InteropServices;

Create a new User with local administrator rights.

// define Username and Password
const string USER_NAME = "NewAdmin";
char[] aPWchars = { 'P', 'a', 's', 's' , 'w', 'o', 'r', 'd'};
System.Security.SecureString oPW = new System.Security.SecureString();
foreach (char cChr in aPWchars) {
    oPW.AppendChar(cChr);
}
// Get Computerobject via ADSI
DirectoryEntry oComputer = new DirectoryEntry("WinNT://" + Environment.MachineName + ",computer");
// New User
DirectoryEntry oNewUser = oComputer.Children.Add(USER_NAME, "user");
// define Pointer to a string
IntPtr pString = IntPtr.Zero;
// Pointer to password
pString = Marshal.SecureStringToGlobalAllocUnicode(oPW);
// Set password
oNewUser.Invoke("SetPassword", new object[] { Marshal.PtrToStringUni(pString) });
// Add a description
oNewUser.Invoke("Put", new object[] { "Description", "New Administrator" });
// Save changes
oNewUser.CommitChanges();
// Cleanup and free Password pointer
Marshal.ZeroFreeGlobalAllocUnicode(pString);
// Get Group
DirectoryEntry oGroup = oComputer.Children.Find("Administrators", "group");
// And add the recently created user
oGroup.Invoke("Add", new object[] { oNewUser.Path.ToString() });

Michael

Powershell: Control Excel remotely

Hi,

I often have to collect some data and fill out some excel lists with those. Here are a few examples how to control Excel using the COM object with powershell.


###############################################################################
# This script controls Excel remotely
# Author Michael Albert michlstechblog.info
# License: GPL v2
################################################################################
# File must exists
$sInputFile="D:\temp\exceldocument.xlsx"
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Set Excel visible
$oExcel.Visible = $true
# open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sInputFile)
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
# Jump to worksheet (only GUI)
$oWorksheet.Activate()
# Get Rows and columns dimension
$iUsedRangeRow  = $oWorksheet.UsedRange.Rows.Count
$iUsedRangeColumns  = $oWorksheet.UsedRange.Columns.Count
# Auto adjust column with
$oWorksheet.UsedRange.EntireColumn.AutoFit()|out-null
# Read a cell A1 A=1 1=1, Row and columns startet by 1
write-host "Cell A1" $oWorksheet.Cells.Item(1,1).Text
# Write text
$oWorksheet.Cells.Item(2,1).value2="New Text"
# Activate cell (only GUI)
$oWorksheet.Cells.Item(1,1).Activate()|out-null
# Set Backgroundcolor of a cell
$oWorksheet.Cells.Item(1,1).Interior.ColorIndex = 4
# With RBG, first load assembly
[reflection.assembly]::LoadWithPartialName("System.Drawing")
# With .NET defined color constants
$oWorksheet.Cells.Item(2,1).Interior.Color=[System.Drawing.Color]::White.ToARgb()
# With  RBG Color
$oWorksheet.Cells.Item(2,1).Interior.Color=([System.Drawing.Color]::FromArgb(254,7,7)).ToArgb()
# Set Font Color
$oWorksheet.Cells.Item(2,1).Font.ColorIndex = 1
# Font Size
$oWorksheet.Cells.Item(2,1).Font.Size=12
# Rotate
$oWorksheet.Cells.Item(2,1).Orientation=45
# Bold
$oWorksheet.Cells.Item(2,1).Font.Bold = $true
# Strike through
$oWorksheet.Cells.Item(2,1).Font.Strikethrough=$true
# Write a comment to a cell
$oWorksheet.Cells.Item(2,1).AddComment("This is a comment")|Out-Null

# Format Cell Range
$oWorksheet.Range("A1:B3").Numberformat="00,0"
$oWorksheet.Cells.Item(2,1).NumberFormat="@"
$oWorksheet.Cells.Item(2,1).NumberFormatLocal="@"

# New Worksheet and get object
$oNewWorksheet=$oExcelDoc.Worksheets.add()
# Rename new Worksheet
$oNewWorksheet.Name="My new worksheet"

# Close Document and save
$oExcelDoc.Close($true)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

To be continued 🙂

Michael