fill up excel sheet

[Azarou]

I got a little script to count vm in a vcenter.

data is pulled into an xls file "report.xls"

evrything is working fine but when i ran the script it overwrite the old one ( and the data )

t want to know if it's possible to do that :   each time i run the script it keep data in row 2 but fill data in row 3 with the new date.

if someone can help iam stuck

Thanks a lot

here my script

$TotalVMs = Get-VM

$TotalVMsCount = $TotalVMs.count

$xlsfile = "D:\report.xls"

$date = Get-Date -UFormat "%d / %m / %Y"

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.add()

$Sheet = $Excelok.Worksheets.Item(1)

$Sheet.Cells.Item(1,1)  = "Date"

$Sheet.Cells.Item(1,2)  = "Count of VM"

$intRow = 2

$WorkBook = $Sheet.UsedRange

$WorkBook.Interior.ColorIndex = 19

$WorkBook.Font.ColorIndex = 11

$WorkBook.Font.Bold = $True

$Sheet.Cells.Item($intRow, 1)  = $date

$Sheet.Cells.Item($intRow, 2)  = $TotalVMsCount

$WorkBook.EntireColumn.AutoFit()

$Sheet.SaveAs($xlsfile)

[LucD]

If you start with $IntRow = 3, doesn't it then skip row 2 ?

[Azarou]

hi,

yes with $IntRow = 3 it skip row 2. But i am trying to get a way to do it automatically.

I was thinking about a fonction to check if row is populated if yes then skip and write in row + 1

[LucD]

Have a look at the answer in this one

Seems you can use the SpecialCells function

[Azarou · Accepted solution]

Hi,

Thanks for your help, but your link is too tricky for me

I found a better way to do what i wanted. Thanks for helping me finding the way !

$TotalVMs = Get-VM

$TotalVMsCount = $TotalVMs.count

$Date = Get-Date -UFormat "%d / %m / %Y"

$xlsfile = "\report.xls"

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel.DisplayAlerts = $False

$Excel = $Excel.Workbooks.open($xlsfile)

$Sheet = $Excel.Worksheets.Item(1)

$objRange = $Sheet.UsedRange

$RowCount = $objRange.Rows.Count

$IntRow = ($RowCount + 1)

$Sheet.Cells.Item(1,1)  = "Date"

$Sheet.Cells.Item(1,2)  = "Number of VM"

$Sheet.Cells.Item($IntRow, 1)  = $Date

$Sheet.Cells.Item($IntRow, 2)  = $TotalVMsCount

$WorkBook = $Sheet.UsedRange

$WorkBook.EntireColumn.AutoFit()

$Sheet.SaveAs($xlsfile)

$Excel.Workbooks.Close()

$Excel.Quit()