# Excel file information

$excelFilePath = "I:\Jason Stephens\hpfDailyChecks.xls"



# Open Excel and display to the screen

$excel = New-Object -comobject Excel.Application

$excel.Visible = $True

$workbook = $excel.Workbooks.Open($excelFilePath)



# Create sheet for current year if it does not already exist

ForEach ($worksheet in $workbook.Worksheets)

{ 

	If ($worksheet.name -eq (Get-Date -Format "yyyy")) 

	{  

		write-host "TADA!" 

	} 

	write-host $worksheet.name

}



If ($workbook.sheets | Where-Object {$_.name -eq (Get-Date -Format "yyyy")} -eq $null)

{ 

	write-host "in if statement"

}



# Shut down excel

$excel.Quit()

As you can see, I am floundering a bit here. I have a workbook with 2 sheets named

"General" and "2008". The question is, what happens when 2009 comes around?

I would like my script to automatically create and start using the new sheet.

The first little thing I have there is a loop through all the pages. That isn't

really as smooth as the if statement that I have. I would rather have the if

statement if possible instead of using a flag after the loop to create it if it

is false. I think the problem is that my WHERE-OBJECT is returning and empty

collection. How do I deal with that?

On a side note, the excel.quit() doesn't shut down excel in my processes.

I have read about this but the solutions I have found don't seem to work.

Any help with this would also be greatly appreciated! :)

That is what I tried as far as the com object. Perhaps I need to revisit that particular code. The "IF" statement is what is really bothering me at the moment :(

Well, I suspect the problem is that you've not actually saved yet (or chose not to), so you're not being allowed to close the process.  I can't remember the syntax right now, but will look it up tomorrow.

#------------------------------------------------------------------------------
# Excel file information
$excelFilePath = "I:\Jason Stephens\hpfDailyChecks.xls"
#------------------------------------------------------------------------------
function Release-Ref ($ref) 
{ 
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
    [System.__ComObject]$ref) -gt 0) 
    [System.GC]::Collect() 
    [System.GC]::WaitForPendingFinalizers() 
} 
#------------------------------------------------------------------------------
# Open Excel and display to the screen
$excel = New-Object -comobject Excel.Application
$excel.Visible = $TRUE
$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.sheets.Item(1)

# Create sheet for current year if it does not already exist
#If ($workbook.sheets | Where-Object {$_.name -eq (Get-Date -Format "yyyy")} -eq $null)
#{
#    write-host "in if statement"
#}

# Save and shut down excel
$workbook.Save()
$excel.Quit()
$a = Release-Ref($worksheet) 
$a = Release-Ref($workbook) 
$a = Release-Ref($excel)

I used code from Kent Finkle's Code Page. It appears to have fixed the open excel processes after I close. Is there a better, shorter, or cleaner way to shut it down?

But back to my original question. What is the best way to check for the current year sheet and create it if it isn't already created?