asked on

When creating and Excel file why always get Theaction can't be completed..........open in Windows PowerShell

I have a script that creates an excel file, but if i want to delete the file manually i cannot because is opened by windows powershell. I have to exit of the Powershell then I can delete the file.
thanks,

ASKER CERTIFIED SOLUTION

SubSun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

namerg

ASKER

Hmm, I am closing but I am not Quiting...let me try..

namerg

ASKER

You got it. The key was $xl.Quit()

namerg

ASKER

Hmm, too quick....still not working the EXCEL process still running....

SubSun

When I test, I am able to delete the file even if the excel process is running.. Can you post your code so I can test and see what is wrong...

namerg

ASKER

Damnn... I do not know what I did :(
Now I am getting Unable to index into an object of type System.DateTime.
At C:\scripts\VMware\HealthCheck_vCenter2_xls.ps1:292 char:51
+ $Sheet.Cells.Item($intRow,$intColumn) = $array2[ <<<< $i]
+ CategoryInfo : InvalidOperation: (0:Int32) [], RuntimeException
+ FullyQualifiedErrorId : CannotIndex
Yes, I will post the code. But, has powercli, will not matter ?

SubSun

It's ok..

namerg

ASKER

Here it is....

####################################
# VMware VirtualCenter server name #
####################################
$vcserver="COMPANYvcenter2.COMPANYcolo.pvt"

##################
# Add VI-toolkit #
##################
#Add-PSsnapin VMware.VimAutomation.Core
#Initialize-VIToolkitEnvironment.ps1
connect-VIServer $vcserver

#############
# Variables #
#############
$vcversion = get-view serviceinstance
$snap = get-vm | get-snapshot
$date=get-date

#Creating an Excel Object
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel = $Excel.Workbooks.Add()

#############################
# Add Text to the EXCEL file #
#############################

#Configuring 1st sheet of the Excel workbook
$Sheet = $Excel.Worksheets.Item(1)
$Sheet.Name = "VMware Health COMPANYvCenter2"
$Sheet.Cells.Item(1,1) = "VMware Health COMPANYvCenter2"
$Sheet.Cells.Item(2,1) = "Date and time"
$Sheet.Cells.Item(3,1) = $date
$Sheet.activate()

$Sheet.application.activewindow.splitrow = 1
$Sheet.application.activewindow.freezepanes = $true

$WorkBook = $Sheet.UsedRange
#$WorkBook.Interior.ColorIndex = 11
#$WorkBook.Font.ColorIndex = 19
$WorkBook.Font.Bold = $True

#######################
# VMware ESX hardware #
#######################
Write-Host "VMware ESX hardware"
$array0 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty Vendor
$array1 =  Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty Model
$array2 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty MemorySize
$array3 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty CpuModel
$array4 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty CpuMhz
$array5 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty NumCpuCores
$array6 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty NumCpuThreads
$array7 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty NumNics
$array8 = Get-VMHost | Get-View | ForEach-Object { $_.Summary.Hardware } | Select-object -ExpandProperty NumHBAs

$Sheet.Cells.Item(5,1).font.bold=$true
$Sheet.Cells.Item(5,1) = "VMware ESX server Hardware configuration" 
$Sheet.Cells.Item(6,1).font.bold=$true
$Sheet.Cells.Item(6,1) = "Vendor"
$Sheet.Cells.Item(6,2).font.bold=$true
$Sheet.Cells.Item(6,2) = "Model"
$Sheet.Cells.Item(6,3).font.bold=$true
$Sheet.Cells.Item(6,3) = "Memory Size (GB)"
$Sheet.Cells.Item(6,4).font.bold=$true
$Sheet.Cells.Item(6,4) = "CPU Model"
$Sheet.Cells.Item(6,5).font.bold=$true
$Sheet.Cells.Item(6,5) = "CPU Mhz"
$Sheet.Cells.Item(6,6).font.bold=$true
$Sheet.Cells.Item(6,6) = "CPU Cores"
$Sheet.Cells.Item(6,7).font.bold=$true
$Sheet.Cells.Item(6,7) = "CPU Threads"
$Sheet.Cells.Item(6,8).font.bold=$true
$Sheet.Cells.Item(6,8) = "Num Nics"
$Sheet.Cells.Item(6,9).font.bold=$true
$Sheet.Cells.Item(6,9) = "Num HBAs"

$i = 0
$intRow = 7
$intColumn = 1
$intHwColMax = 9
$xlCenter = -4108
$Rounding = 0

Do {
	#Row 7, Column 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array1[$i]
	$intColumn = $intColumn + 1
	$Rounding = [math]::round((($array2[$i] / "1048576") / "1024"),2)
	$Sheet.Cells.Item($intRow,$intColumn) = $Rounding
	$Sheet.Cells.HorizontalAlignment = $xlCenter
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array3[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array4[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array5[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array6[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array7[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array8[$i]
				
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )


#######################
# VMware ESX versions #
#######################
#Write-Host "VMware ESX Versions"
$array0 = @()
$array1 = @()
$array2 = @()

$array0 = Get-VMHost | Select Name | Select-object -ExpandProperty Name
$array1 = get-vmhost | Select Version | Select-object -ExpandProperty Version
$array2 = get-vmhost | Select Build | Select-object -ExpandProperty Build

$i = 0
#$intRow equals 17
$intRow = $intRow + 2
$intColumn = 1
$intHwColMax = 9

#$intRow equals 20
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VMware ESX server versions and builds" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "HOST Name"
$Sheet.Cells.Item($intRow,2).font.bold=$true
$Sheet.Cells.Item($intRow,2) = "Version"
$Sheet.Cells.Item($intRow,3).font.bold=$true
$Sheet.Cells.Item($intRow,3) = "Build"
$intRow = $intRow + 1

Do {
	#Row 14, Column 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array1[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array2[$i]
	$intColumn = $intColumn + 1
					
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )

######################
# VMware VC version  #
######################
#Write-Host "VMware VC Version"
$array0 = @()
$array1 = @()
$array2 = @()

$array0 = @($DefaultVIServers | Select-Object -ExpandProperty Name)
$array1 = @($DefaultVIServers | Select-Object -ExpandProperty Version)
$array2 = @($DefaultVIServers | Select-Object -ExpandProperty Build)

$i = 0
#$intRow equals 32
$intRow = $intRow + 2
$intColumn = 1
$intHwColMax = 9

#$intRow equals 35
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VMware VirtualCenter version" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "vCenter Name"
$Sheet.Cells.Item($intRow,2).font.bold=$true
$Sheet.Cells.Item($intRow,2) = "Version"
$Sheet.Cells.Item($intRow,3).font.bold=$true
$Sheet.Cells.Item($intRow,3) = "Build"
$intRow = $intRow + 1

Do {
	#Row 21, Column 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array1[$i]
	$intColumn = $intColumn + 1
	$Sheet.Cells.Item($intRow,$intColumn) = $array2[$i]
	$intColumn = $intColumn + 1
					
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )


#############
# Snapshots # 
#############
#Write-Host "VMware Snapshots"
$array0 = @()
$array1 = @()
$array2 = @()
$array3 = @()

$array0 = Get-VM | get-snapshot | %{$_.VM.Name}
$array1 = Get-VM | get-snapshot | select-object -expandproperty Name
$array2 = Get-VM | get-snapshot | select-object -expandproperty Created 
$array3 = Get-VM | get-snapshot | select-object -expandproperty Description

$i = 0
#$intRow equals 37
$intRow = $intRow + 2
$intColumn = 1
$intHwColMax = 9

#$intRow equals 40
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "Snaphots Active" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VM Name"
$Sheet.Cells.Item($intRow,2).font.bold=$true
$Sheet.Cells.Item($intRow,2) = "Name"
$Sheet.Cells.Item($intRow,3).font.bold=$true
$Sheet.Cells.Item($intRow,3) = "Created"
$Sheet.Cells.Item($intRow,4).font.bold=$true
$Sheet.Cells.Item($intRow,4) = "Description"
$intRow = $intRow + 1

If ($array0 -ne $null) {
	Do {
		#Row 26,1
		$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
		$intColumn = $intColumn + 1
		$Sheet.Cells.Item($intRow,$intColumn) = $array1[$i]
		$intColumn = $intColumn + 1
		$Sheet.Cells.Item($intRow,$intColumn) = $array2[$i]
		$intColumn = $intColumn + 1
		$Sheet.Cells.Item($intRow,$intColumn) = $array3[$i]
		$intColumn = $intColumn + 1
					
		$i ++
		$intRow ++
		$intColumn = 1
	} Until ( $array0[$i] -eq $null )
}
Else 
	{
		$Sheet.Cells.Item($intRow,$intColumn) = "No Snapshots"
	}

#################################
# VMware CDROM connected to VMs # 
#################################
#Write-Host "VMware CDRoms Mounted"
$array0 = @()
$array1 = @()
$array2 = @()

$array0 =@(Get-VM | where { $_ | Get-CDdrive | where { $_.ConnectionState.Connected -eq "true" } } | Select-object -ExpandProperty Name)

$i = 0
#$intRow equals 48
$intRow = $intRow + 2
$intColumn = 1

$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "CDROMs connected" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VM Name"
$intRow = $intRow + 1
Do {
	#52,1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
					
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )


#########################
# Datastore information #
#########################
#Write-Host "Datastore Information"
$array0 = @()
$array1 = @()
$array2 = @()

$array0 = Get-Datastore | Select-Object -ExpandProperty Name
$array1 = Get-Datastore | Select-Object -ExpandProperty FreeSpaceGB
$array2 = Get-Datastore | Select-Object -ExpandProperty CapacityGB


$i = 0
$Percent = "%"
$String0 = ""

#$intRow equals 50
$intRow = $intRow + 2
$intColumn = 1

$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "Datastore Space Available" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "Datastore"
$Sheet.Cells.Item($intRow,2).font.bold=$true
$Sheet.Cells.Item($intRow,2) = "UsedGB"
$Sheet.Cells.Item($intRow,3).font.bold=$true
$Sheet.Cells.Item($intRow,3) = "Free GB"
$Sheet.Cells.Item($intRow,4).font.bold=$true
$Sheet.Cells.Item($intRow,4) = "Perc Free"
$intRow = $intRow + 1

Do {
	#52,1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
	
	$UsedSpace = [math]::round(($array2[$i] - $array1[$i]),2)
	$Sheet.Cells.Item($intRow,$intColumn) = $UsedSpace
	$intColumn = $intColumn + 1
	
	$FreeSpace = [math]::round($array1[$i],2)
	$Sheet.Cells.Item($intRow,$intColumn) = $FreeSpace
	$intColumn = $intColumn + 1
	
	$PercFree = [math]::Round((100 * $array1[$i] / $array2[$i]),0)
	$String0 = "$PercFree$Percent"
	$Sheet.Cells.Item($intRow,$intColumn) = $String0
	$intColumn = $intColumn + 1
					
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )

$array0 = @()
$array1 = @()
$array2 = @()

#####################
# VMs per Datastore #
#####################
#Write-Host "VMs per Datastore"
$array0 = @()
$array0 =  Get-Datacenter | Get-VM | %{$vm = $_ ; Get-Datastore -VM $vm | %{$vm.Name + "--" + $_.Name} }

$i = 0
$flag = 0
$intRow = $intRow + 2
$intColumn = 1

#$intRow equals 87
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VMs per Datastore (VM--Datastore)" 
$intRow = $intRow + 1

Do {
	#88,1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$i ++
	$intRow ++
} Until ( $array0[$i] -eq $null )

##################
# VM information #
##################
#Write-Host "VM information"
$array0 = @()
$array1 = @()
$array3 = @()
$array4 = @()
$array5 = @()
$array6 = @()
$array7 = @()
$array8 = @()
$array9 = @()
$array10 = @()
$array11 = @()

$array0 = Get-VM | Get-View | ForEach-Object { $_.Name }
$array1 = Get-VM | Get-View | ForEach-Object { $_.Guest.Hostname }
$array2 = Get-VM | Get-View | ForEach-Object { $_.Guest.ipAddress }
$array3 = Get-VM | Get-View | ForEach-Object { $_.Guest.Net } | select Network
$array4 = Get-VM | Get-View | ForEach-Object { $_.summary.runtime.powerState }
$array5 = Get-VM | Get-View | ForEach-Object { $_.summary.config.numcpu }
$array6 = Get-VM | Get-View | ForEach-Object { $_.summary.config.memorysizemb }
$array7 = Get-VM | Get-View | ForEach-Object { $_.summary.quickStats.guestMemoryUsage }
$array8 = Get-VM | Get-View | ForEach-Object { $_.summary.config.numEthernetCards }
$array9 = Get-VM | Get-View | ForEach-Object { $_.guest.toolsstatus }
$array10 = Get-VM | Get-View | ForEach-Object { $_.config.tools.toolsversion }
$array11 = Get-VM | Get-View | ForEach-Object { $_.Guest.GuestFullName }

$i = 0
$intRow = $intRow + 2
$intColumn = 1

$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "Virtual Machine information" 
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).font.bold=$true
$Sheet.Cells.Item($intRow,1) = "VM Name"
$Sheet.Cells.Item($intRow,2).font.bold=$true
$Sheet.Cells.Item($intRow,2) = "Hostname"
$Sheet.Cells.Item($intRow,3).font.bold=$true
$Sheet.Cells.Item($intRow,3) = "IP Address"
$Sheet.Cells.Item($intRow,4).font.bold=$true
$Sheet.Cells.Item($intRow,4) = "Network Name"
$Sheet.Cells.Item($intRow,5).font.bold=$true
$Sheet.Cells.Item($intRow,5) = "Power State"
$Sheet.Cells.Item($intRow,6).font.bold=$true
$Sheet.Cells.Item($intRow,6) = "Num CPU"
$Sheet.Cells.Item($intRow,7).font.bold=$true
$Sheet.Cells.Item($intRow,7) = "Mem Size (GB)"
$Sheet.Cells.Item($intRow,8).font.bold=$true
$Sheet.Cells.Item($intRow,8) = "Guest Mem Usage"
$Sheet.Cells.Item($intRow,9).font.bold=$true
$Sheet.Cells.Item($intRow,9) = "Num Ethernet Cards"
$Sheet.Cells.Item($intRow,10).font.bold=$true
$Sheet.Cells.Item($intRow,10) = "Tools Status"
$Sheet.Cells.Item($intRow,11).font.bold=$true
$Sheet.Cells.Item($intRow,11) = "Tools Version"
$Sheet.Cells.Item($intRow,12).font.bold=$true
$Sheet.Cells.Item($intRow,12) = "OS Version"
$intRow = $intRow + 1


Do {
	#88,1
	$Sheet.Cells.Item($intRow,$intColumn) = $array0[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array1[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array2[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array3[$i].Network
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array4[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array5[$i]
	$intColumn = $intColumn + 1
	
	$Rounding = [math]::round(($array6[$i] / "1024"),2)
	$Sheet.Cells.Item($intRow,$intColumn) = $Rounding
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array7[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array8[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array9[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array10[$i]
	$intColumn = $intColumn + 1
	
	$Sheet.Cells.Item($intRow,$intColumn) = $array11[$i]
	$intColumn = $intColumn + 1
	
	$i ++
	$intRow ++
	$intColumn = 1
} Until ( $array0[$i] -eq $null )


##############################
# Disconnect session from VC #
##############################

disconnect-viserver -confirm:$false
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("B1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("C1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("D1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("E1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("F1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("G1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("H1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("I1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("J1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("K1")
$WorkBook.EntireColumn.AutoFit()

$WorkBook = $Sheet.Range("L1")
$WorkBook.EntireColumn.AutoFit()

$Excel.SaveAs("c:\scripts\vmware\HealthCheckCOMPANYvCenter2.xlsx")
#$WorkBook.SaveAs("c:\scripts\vmware\HealthCheckCOMPANYvCenter.xlsx")
$Excel.Close()
#$Excel.Quit()
Start-Sleep 15
######################
# E-mail HTML output #
######################
# Mail variables #
$enablemail="yes"
$emailattachment = "C:\scripts\VMware\HealthCheckCOMPANYvCenter2.xlsx"
$smtpServer = "ux3.COMPANYcolo.pvt" 
$mailfrom = "VMware Healtcheck <VMWareTech@COMPANYcolo.pvt>"
#$mailto = "COMPANY-NetworkGroup@COMPANYcolo.org"
$mailto = "german.rosero@COMPANYcolo.org"

if ($enablemail -match "yes") 
{ 
	$msg = new-object Net.Mail.MailMessage
	$att1 = new-object Net.Mail.Attachment($emailattachment)
	$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
	$msg.From = $mailfrom
	$msg.To.Add($mailto) 
	$msg.Subject = "VMware Health COMPANYvCenter2"
	$msg.Body = "VMware HealthCheck COMPANYvCenter2"
	$msg.Attachments.Add($att1)
	$smtp.Send($msg)
}


##########################
# End Of HealthCheck_COMPANYvCenter_xls.ps1 #
##########################

Open in new window

namerg

ASKER

Now, I know why is breaking...there is a snapshot but as a date format, so that is why cannot be converted: Lines 214 - 217

PowerCLI C:\scripts\VMware> Get-VM | get-snapshot | %{$_.VM.Name}
US10-EMC
PowerCLI C:\scripts\VMware> Get-VM | get-snapshot | select-object -expandproperty Name
Wed Jun 19 07:56:29 2013
PowerCLI C:\scripts\VMware> Get-VM | get-snapshot | select-object -expandproperty Created
Wednesday, June 19, 2013 7:57:18 AM
PowerCLI C:\scripts\VMware> Get-VM | get-snapshot | select-object -expandproperty Description
PowerCLI C:\scripts\VMware>

Open in new window

SubSun

I thought I posted in your question (Or I might posted this comment on another question ;-P )
Run the script by adding $att1.Dispose() after $smtp.Send($msg) and see if you can delete the excel file

namerg

ASKER

You got it. Subsun. That was it