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

VMware: Computer Account is disabled after deploying Windows Clients

Hello,

last week I had to deploy 25virtual Windows (XP) Clients an on VMware ESX Cluster. One of the requirements was that the clients must be member of a Windows Active Directory.

After setting up vCenter (copy sysprep) I installed a reference Windows XP vm, convert them to a template and defined a Customization Specification like this:

Initial Customization Specification
Initial Customization Specification

During the cloning process, by using the template with the recently created custom spec, the domain join always fails and the Computer Account was disabled at each try.

Ok, now I tried modify some parameter of the Custom Specification:

  • Changing the Domain from fqdn to the NetBIOS Domain “SUB”
  • Altered the Username to SUB\joinuser or joinuser@SUB or joinuser@sub.domain.org

Continue reading VMware: Computer Account is disabled after deploying Windows Clients

Windows Vista: Cannot install Service Pack 2

Hello,

a customer had the problem that he couldn’t install the Windows Vista Service Pack 2 on his Laptop. Service Pack 1 seems to be installed, but the Service Pack 2 Setup says it isn’t.

Service Pack 2 Setup do not recognize Service Pack 1
Service Pack 2 Setup do not recognize Service Pack 1

I processed a query by using the WMI Command line tool and filtered for Service pack 1 (Hotfixnumber KB936330).

wmic qfe|findstr /I 936330

But in the list of installed hotfixes KB936330 is not listed. WinVer.exe reports a Vista with Service Pack 1 installed.

WinVer report
WinVer report

Continue reading Windows Vista: Cannot install Service Pack 2

VMware: Cannot connect from vCenter Webclient to VDP Appliance

Hi,

the license of VMware vSphere 5.1 includes the diskbased Backup Solution VDP(vSphere Data Protection). So I’ve decited to try the appliance (Version 5.1 Timestamp 2012-10-02) whether these meets our requirements.

Before I started, I ensured that

  • The DNS forward and reverse resolution of the choosen name and IP address is possible.
  • The User, who manage the backup, have vCenter Administrator rights.
  • all prerequisites are fulfilled

Downloading and installing of the appliance works as expected. The Appliance is properly registered in the vSphere Webclient but I can’t start the VDP Frontend within.

Error Message
Error Message

Error: Could not connect to the requested VDP appliance. Would you like to be directed to the VDP configuration screen to throubleshoot the issue.

But whats wrong? I’m currently logged on to the Webclient with my Domain Login. When I’m using SSO Administrator admin@system-domain Login the VDP Frontends starts,
also another Domain Login can connect. Something in the chain Windows Domain, Single Sign On, vCenter and VDP Appliance fails.
Continue reading VMware: Cannot connect from vCenter Webclient to VDP Appliance

My Knowledgebase for things about Linux, Windows, VMware, Electronic and so on…