afokkema Microsoft

Powershell: Working with Excel and error HRESULT: 0x80028018


 image

If you want to use Microsoft Excel in your Powershell scripts. 

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

 

You can run into the following error:

image

The work around for this issue was changing the Regional Options back to United States International.

image

Microsft created a work around. More info can be found in in: KB320369

guillermooo has created a port to Powershell. Just copy the following code and you should be able to open Excel and create a new workbook.

I didn’t test this script block so let me know if it works 😉

$ci = new-object system.globalization.cultureinfo "en-US"

$e = New-Object -COM "Excel.Application"
$e.Visible = $True
$e.UserControl= $True
$books = $e.Workbooks
$books.PSBase.GetType().InvokeMember( `
       "Add", `
       [system.reflection.bindingflags]::InvokeMethod, `
       $null, $books, $null, $ci)

Source: http://stackoverflow.com/questions/687891/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add

afokkema Powershell

How To: Uninstall Powershell 1.0


image

If you’re trying to install Powershell 2.0 CTP3 on a Windows PC with Powershell 1.0 already installed, the installation wizard will stop with the following warning.

image

Before the wizard will continue, you have to uninstall Powershell 1.0.

Open Control Panel. Select Show updates and browse to Windows XP – Software Updates. Click on Remove to uninstall Powershell 1.0.

UninstallPowerShell1

If you don’t have an entry in the Add/Remove Programs control panel, You can find the Powershell 1.0 Uninstaller here:

  • C:\WINDOWS\$NtUninstallKB926139-v2$\spuninst\spuninst.exe
  • C:\WINDOWS\$NtUninstallKB926139$\spuninst\spuninst.exe

After uninstalling Powershell 1.0 you’re able to install Powershell 2.0 CTP3.

afokkema Powershell

Posh: Run gpupdate on Multiple Computers


image

I created a Powershell script that will get all the computers from a certain OU and run GPUpdate /force on these machines. This script uses the Quest Active Directory cmdlets and PsExec.exe. You need to install the Quest cmdlets first. If PsExec doesn’t exist, the script will download it to the c:\tools directory.

When you start the script, you have to enter the FQDN and the OU name:

image

The script creates a text file with al the computer names in it. This text file will be used with the PsExec.exe command.

image

You can find the script on poshcode.org

afokkema Powershell, VI Toolkit

Restart VMware Tools on all Windows VM’s


 image

After reading the post on http://www.virtualvcp.com/content/view/82/1/ about the VMware Tools status “not running” and in particular the part about the preferred work around:

I find that restarting the VMware Tools Service in the guest OS always gets by the problem, but loggin into every single VM that reports the wrong status for it’s VMware Tools could be a bit of a drag. So I choose to do this remotely rather that logging on to each VM.

From any Windows workstation/server, open a command pompt and run:

sc \\{vm-name-or-ip-address} stop "VMTools"
sc \\{vm-name-or-ip-address} start "VMTools"

I thought that can be done via Powershell and the VI Toolkit. So I created the following script that will restart the VMware Tools service on every running Windows VM. 

$vCenter = Read-Host "Enter the vCenter servername"

Connect-VIServer $vCenter

$Service = "VMtools"
$VMs = Get-VM | Where-Object {
        $_.PowerState -eq "PoweredON" `
        -and `
        $_.Guest.OSFullName -match "Windows"
    }
    
foreach($VM in $VMs)
{
    Write-Host "-------------------------------------------"
    Write-Host "Restarting the VMware Tools Service on" $VM
        $Svc = Get-WmiObject -Computer $VM win32_service `
        -filter "name='$Service'"
            $Result = $Svc.StopService()
            sleep 5
            $Result = $Svc.StartService()
    Write-Host "Done.. "
    Write-Host "-------------------------------------------"
}

Disconnect-VIServer -Confirm:$false

This script generates the following output:

image

Other useful blog posts or kb articles on this subject:

Source for the restart service part in my script: http://blog.geekpoet.net/2008/10/manipulating-remote-services-with.html

afokkema Powershell

Powershell: Get-SubFolderSize


image 

On every home directory will be a  folder called Files. I was curious how big these folders are. So this is why I created the following Powershell script:

function Get-Foldersize{
  param([string]$StartFolder,
        [string]$SubFolder)
  
  $Dirs = Get-ChildItem -path $StartFolder\* -Recurse -Include $SubFolder 
    
  foreach($Dir in $Dirs){
  $objFSO = New-Object -com  Scripting.FileSystemObject
  "$Dir = " + "{0:N2}" -f (($objFSO.GetFolder("$Dir").Size) / 1MB) + " MB" 
  }
}

Get-FolderSize C:\Users Files

 

The output of the script will look like this:

image

Here are some pictures (as hard evidence 😉 ) of the folder properties:

image

Source: http://www.microsoft.com/technet/scriptcenter/

afokkema Powershell

Beta: Vizioncore Virtualization EcoShell


image

Vizioncore released the beta version of the Virtualization EcoShell.

The goal of the Vizioncore Virtualization EcoShell is to provide a freeware desktop application for novice and expert IT administrators leveraging Windows PowerShell scripts across their multi-platform virtual environments.   

Fostered and supported by The Virtualization EcoShell Initiative (VESI) – an online community-driven Web site sponsored by Vizioncore – the Virtualization EcoShell is enhanced by the participation of community members through the exchange of new ideas, value-add services and extendable scripts. To become a member of the VESI community, please visit VESI Registration.

 

One of the best features is based on a script from Alan Renouf (vdiagram) and it’s called Generate vDiagram.

image

With this feature/script you can generate a Visio drawing of your VI environment. You can choose the following options:

image

If all the options are set to true, the script will generate three workspaces in Visio with three different vDiagrams. The diagram will look like this:

image

If you are managing a VI environment, this will be a must have tool. So take a look and get your download here: http://www.thevesi.org/downloads.jspa

afokkema Powershell

Powershell: Add User to a lot of Groups


image 

Update: Dimitry Sotnikov has posted a great article about the new cmdlets. You can read his post here.  After reading Dimitry’s post, I created a new script.

For some test purposes I created a new test user. This test user has to be a member off al my application groups in Active Directory. This job can easily  be done with Powershell and the Quest AD cmdlets. 

 
$User = Read-Host ("Username")
Get-QADGroup -Name 'GG_APL_*' -NotContainsIndirectMember $User `
    | Add-QADGroupMember -Member $User

 

To verify the changes, you can run the following script:

$User = Read-Host ("Username")
Get-QADGroup $User | Select Name
afokkema Microsoft

How To: Slipstream Office 2007 SP1


image

The slipstream process of Office 2007 is a simple two step process:

  1. Extract the SP1 file
  2. Copy the MSP files to your install share

Download Office 2007 SP1 here: http://www.microsoft.com/downloads/. Save the file on the root of your D:\ drive. Create a folder called D:\Office2007MSP\.

Extract the SP1 file with the following command:
D:\office2007sp1-kb936982-fullfile-en-us.exe /extract:D:\Office2007SP1MSP

image

Open the folder D:\OfficeSP1MSP

image

Copy all the *.msp files to the Updates folder on your install share.

image

If you run Setup.exe the setup wizard will install Office 2007 SP1.

Source: http://www.msfn.org/board/lofiversion/index.php/t109038.html