Category Archives: Scripting & programming

Somthing about Powershell, VB Script, C#, VB.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