need help to add worksheet to existing xlsx via PowerShell module "PSExcel"

Question

I'm working on a project to create some Excel reports with data from MSSQL databases and need some help with the end result.

Disclaimer - I'm not great with PowerShell and MSSQL).

So far, with the help of the internet, I've managed to create a .ps1 file that elevates itself, imports a PS module called PSExcel (https://github.com/RamblingCookieMonster/PSExcel), imports the SQLPS module and runs three separate queries to export to three separate .xlsx files.

My script for the last few parts is:

# Import PSExcel module
Import-Module C:\scripts-and-reports\Modules\PSExcel

# Import SQLPS module
Import-Module sqlps

# Import PSExcel module
Import-Module C:\scripts-and-reports\Modules\PSExcel

# Import SQLPS module
Import-Module sqlps

# Create individuals
invoke-sqlcmd -inputfile "C:\scripts-and-reports\individuals.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Individuals" -Path "C:\scripts-and-reports\Individuals.xlsx" -Table -Force

# Create joint parties
invoke-sqlcmd -inputfile "C:\scripts-and-reports\joint-parties.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Joint Parties" -Path "C:\scripts-and-reports\Joint Parties.xlsx" -Table -Force

# Create organisations
invoke-sqlcmd -inputfile "C:\scripts-and-reports\organisations.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Organisations" -Path "C:\scripts-and-reports\Organisations.xlsx" -Table -Force

I've tried to no avail to combine the last two query exports into the first query's export as additional worksheets so that I only have a single Excel workbook to hand to my boss, but I think I must be approaching it incorrectly.

When I read the example on Line 94 of ../Export-XLSX.ps1 and try to implement it in my scenario by changing the file names to match each other, the last query replaces the first in the outputted .xlsx file. This must be because of the -Force. Changing that to -Append won't help because then it says the file already exists.

Can anyone help me by first showing me where I'm going wrong and then pointing me in the right direction (this may end up in a walkthrough).

Please and thanks!

---** UPDATE **---

With @gms0ulman's fix to Export-XLSX.ps1, it looks like it's going to work as I've tested it with different SQL queries to what I need and it adds the worksheets OK. The queries I'm using for the test are

SELECT DISTINCT
case mt.[Status] 
    when 0 then 'In Progress' 
    When 1 then 'On Hold' 
    when 2 then 'Completed' 
    when 3 then 'Not Proceeding' 
else 'Unknown' end as MatterStatus,
mt.LastUpdatedOn as LastModified
from matter mt 
where mt.LastUpdatedOn >= '2016-07-01'  
    AND (mt.[status] = 0 or mt.[status] = 1)

While this (and the other two iterations of it in my PS script) works, my actual queries don't. The queries themselves work, and the first export too, but when -Append is used in PS with the invoke-sqlcmd and invoke-sqlcmd -inputfile "query2.sql" -serverinstance "" -database "" | Export-XLSX -WorksheetName "Joint Parties" -Path "C:\scripts-and-reports\Matter Details.xlsx" -Table -Append, the two appended worksheets get the error:

Exceptions calling "SaveAs" with "1" argument(s): "Error saving file C:\scripts-and-reports\Matter Details.xlsx"

At C:\scripts-and-reports\Modules\PSExcel\Export-XLSX.ps1:496 char:13

$Excel.SaveAs($Path)

~~~~~~~~~~~~~~~~~~~~

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : InvalidOperationException

Answer 1

The problem is with the module you're using. -Append should work. In the Export-XLSX.ps1 file, look at lines 351 and 371.

As you are providing an existing path, 351 evaluates to true and 371 never gets executed. Line 371 is where the module creates a new worksheet for you.

if (($Append -or $ClearSheet) -and ($PSBoundParameters.ContainsKey('Excel') -or (Test-Path $Path)) ) # line 351
{
    $WorkSheet=$Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName}
    if($ClearSheet)
    {
        $WorkSheet.Cells[$WorkSheet.Dimension.Start.Row, $WorkSheet.Dimension.Start.Column, $WorkSheet.Dimension.End.Row, $WorkSheet.Dimension.End.Column].Clear()
    }
    if($Append)
    {
        $RealHeaderCount = $WorkSheet.Dimension.Columns
        if($Header.count -ne $RealHeaderCount)
        {
            $Excel.Dispose()
            Throw "Found $RealHeaderCount existing headers, provided data has $($Header.count)."
        }
        $RowIndex = 1 + $Worksheet.Dimension.Rows
    }
}
else
{
    $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)  #line 371
}

To get around this, I've added a couple of lines to the Export-XLSX.ps1 file. Now, even if the path exists, it will:

• check if a $WorksheetName is provided
• check this worksheet does not already exist
• create new worksheet if both are true.

Note you will have to Remove-Module and Import-Module for changes to be recognised. You will need to use -Append. I used -Force on first worksheet, not the second one. Also, you may find -Verbose helpful as it provides you with more information as the script runs - good for debugging.

if (($Append -or $ClearSheet) -and ($PSBoundParameters.ContainsKey('Excel') -or (Test-Path $Path)) )
{

    # New line: even if path exists, check for $WorkSheetName and that this worksheet doesn't exist
    if($WorkSheetName -and $Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName} -eq $null)
    {
      # if you have $WorksheetName and it doesn't exist, create worksheet.
      $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)
    }
    else
    {
        $WorkSheet=$Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName}
        if($ClearSheet)
        {
            $WorkSheet.Cells[$WorkSheet.Dimension.Start.Row, $WorkSheet.Dimension.Start.Column, $WorkSheet.Dimension.End.Row, $WorkSheet.Dimension.End.Column].Clear()
        }
        if($Append)
        {
            $RealHeaderCount = $WorkSheet.Dimension.Columns
            if($Header.count -ne $RealHeaderCount)
            {
                $Excel.Dispose()
                Throw "Found $RealHeaderCount existing headers, provided data has $($Header.count)."
            }
            $RowIndex = 1 + $Worksheet.Dimension.Rows
        }
    } # this is also new.
}
else
{
    $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)
}