Search code examples
excelpowershellcomworksheet

Powershell Excel Automating adding worksheet

I am attempting to automate the process of adding a worksheet (with data) per clientname in excel for a monthly report type workbook

I thought it should be straight forward... but the method I am using isnt working.... it doesn't even get to the sheet making mode... can you help me figure out what I did wrong?

The following is the function I made

function Excelclientstatstemplate ($clients) {
$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false
$WB = $Exl.Workbooks.Open($excelmonthlysummary) 

$clientws = $WB.worksheets | where {$_.name -like "*$clients*"}

#### Check if Clients worksheet exists, if no then make one with client name ###
$sheetcheck = if (($clientws)) {} Else {


$WS = $WB.worksheets.add
$WS.name = "$clients" 
} 
$sheetcheck 

$WB.Save

# Enter stat labels 
$clientws.cells.item(1,1) = "CPU Count" 
$clientws.cells.item(2,1) =  "RAM"
$clientws.cells.item(3,1) =  "Reserved CPU"
$clientws.cells.item(4,1) =  "Reserved RAM"


### Put in Values in the next column ###

$clientws.cells.item(1,2) =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2) = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2) = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2) = [decimal]$cstats.resmemoryLimitGB 



  $WB.save
  $Exl.quit()
    Stop-Process -processname EXCEL 
    Start-Sleep -Seconds 1

    Echo "$clients excel sheet in monthly summary is done.."
}

and then I tried to make a Foreach thing for it

  $clientxlmonthlywrite = Foreach ($client in $clientlist){

  $cstats = $Combinedstats | Where {$_.Group -eq "$client"}

  Excelclientstatstemplate -clients $client

}

The entire Process of the function goes

  1. Take client name

  2. Open a particular excel workbook

  3. Check if there are any sheets with client name

  4. If there are NO sheets with client name, make one with client name

  5. Fill The first column Cells with labels

  6. Fill the second column cells with data (data works I already write CSVs withem)

  7. Save and exit

The Foreach variable just does the function for each of Clients names from a clientlist (nothing wrong with clientlist)

Am I messing something up?

Thanks for the help.

Solution

  • You are not calling the .Add() method correctly. You are missing the parenthesis at the end of it. To fix it you should be able to simply modify the line to this:

    $WS = $WB.worksheets.add()

    Also, the cells have properties that you should refer to, so I would also modify the part that sets your cell values to something like this:

    # Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"


### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB

    I'm fairly sure that defining the type is pointless, since to Excel they're all strings until you set the cell's formatting settings to something else. I could be wrong, but that is the behavior that I have observed.

    Now, for other critiques that you didn't ask for... Don't launch Excel, open the book, save the book, and close Excel for each client. Open Excel once at the beginning, open the book, make your updates for each client, and then save, and close.

    Test to see if the client has a sheet, and add it if needed, then select the client's sheet afterwords. Right now there's nothing there to set $clientws if you have to add one for that client.

    Adding a worksheet by default places it before the active worksheet. This was a poor choice in design in my opinion, but it is what it is. If it were me I'd add new sheets specifying the last worksheet in the workbook, which will add the new worksheet before the last one, making it the second to the last worksheet. Then I'd move the last worksheet up in front of the new one, effectively adding the new worksheet as the last one listed. Is it possible to add the new worksheet as the last one when you make it? Yes, but it's was too complicated for my taste. See here if you are interested in doing that.

    When testing for an existing client worksheet to make one if it is missing, do that, don't tell it to test for something, and do nothing, and put everything you want in an Else statement. That just complicates things. All that said, here's some of those suggestions put into practice:

    function Excelclientstatstemplate ($clients) {

#### Check if Clients worksheet exists, if no then make one with client name ###
if (($clients -notin $($WB.worksheets).Name)){
    #Find the current last sheet
    $LastSheet = $WB.Worksheets|Select -Last 1
    #Make a new sheet before the current last sheet so it's near the end
    $WS = $WB.worksheets.add($LastSheet)
    #Name it
    $WS.name = "$clients"
    #Move the last sheet up one spot, making the new sheet the new effective last sheet
    $LastSheet.Move($WS)
} 

#Find the current client sheet regardless of if it existed before or not
$clientws = $WB.worksheets | where {$_.name -like "*$clients*"}

# Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"

### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB 

Start-Sleep -Seconds 1

Echo "$clients excel sheet in monthly summary is done.."
}

$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false
$WB = $Exl.Workbooks.Open($excelmonthlysummary) 

$clientxlmonthlywrite = Foreach ($client in $clientlist){
    $cstats = $Combinedstats | Where {$_.Group -eq "$client"}
    Excelclientstatstemplate -clients $client
} 

$WB.save
$Exl.quit()
Stop-Process -processname EXCEL