Problems to save to Excel

[Shashahan]

Dear all,

I am trying to save some data to a specific Excel sheet.
The source is an Excel file (sqlVersionFile) with different worksheets for reach SQL Server Version where I read the data from, then add one more column (SQL Server Version) and then I want to save it to the an existing target (mssqlCMSFile) with the worksheet name 'SQL Versions'.

I tried it with an array of objects and one time using Export-Excel.
I tried it with Export-Excel for each row to save (current listing).
I tried Set-Row.

But whatever I try, I am stuck: With Export-Excel I always get the error

Exception calling "Save" with "0" argument(s): "Error saving file

When using Set-Row instead of Export-Excel I get nothing and nothing is saved to the target.

$i = 1
Set-Row -ExcelPackage $mssqlCMSfileSqlVersionsRecentObject -WorksheetName 'SQL Versions' -Row $i -Value $row
$i++;

Can you please point me in the right direction?

Thank you very much and kind regards,
Olaf

Listing:

$sqlVersionsUrl = "https://aka.ms/sqlserverbuilds";
$sqlVersionFile = "$($env:TEMP)\SqlUpates.xlsx"
$mssqlCMSFile = "C:\Users\UserName\Temp\Mssql_instance_list.xlsx"

$sqlVersions = @(
    '2008 R2'
    , '2012'
    , '2014'
    , '2016'
    , '2017'
    , '2019'
    , '2022'
)

Write-Host "Check if file $($sqlVersionFile) already exists"
if (Test-Path -Path $sqlVersionFile) {    
    Write-Host "File exists, checking age"    
    $lastWrite = (get-item $sqlVersionFile
    ).LastWriteTime
    $timespan = new-timespan -days 30
    if (((get-date) - $lastWrite) -gt $timespan) {
        Write-Host "File older than 30 days, downloading new version"  
        Invoke-WebRequest $sqlVersionsUrl -OutFile $sqlVersionFile
    
    }
}
else {
    Write-Host "File doesn't exist, downloading to $($sqlVersionFile
)"
    Invoke-WebRequest $sqlVersionsUrl -OutFile $sqlVersionFile

}
$mssqlCMSfileSqlVersionsRecentObject = Open-ExcelPackage -Path $mssqlCMSFile -Verbose
#$mssqlCMSfileSqlVersionsHistoryObject = Import-Excel -Path $mssqlCMSFile -WorksheetName 'Sql Versions History'

$sqlVersionRowsArray = @()
Foreach ($version in $sqlVersions) {
    $sqlVersionRows = Import-Excel -Path $sqlVersionFile -WorksheetName $version -AsDate 'Release Date'  
    $sqlVersionRows | Add-Member -MemberType NoteProperty -Name Version -Value $version        
    $row = @{
        'Version'                           = $version
        'Build Number'                      = $sqlVersionRows.'Build Number'.Get(1)
        'KB Number'                         = $sqlVersionRows.'KB Number'.Get(1)
        'KB URL'                            = $sqlVersionRows.'KB URL'.Get(1)
        'Release Date'                      = $sqlVersionRows.'Release Date'.Get(1)
        'Service Pack Level'                = $sqlVersionRows.'Service Pack Level'.Get(1)
        'Cumulative Update or Security ID'  = $sqlVersionRows.'Cumulative Update or Security ID'.Get(1)
        'Servicing Model'                   = $sqlVersionRows.'Servicing Model'.Get(1)
        'Notes'                             = $sqlVersionRows.'Notes'.Get(1)
    }
    $row | Export-Excel -Path $mssqlCMSFile -WorksheetName 'SQL Versions' -Append

    $sqlVersionRowsArray += $sqlVersionRows.Get(1)
}

[scriptingstudio]

You dont need this line: $mssqlCMSfileSqlVersionsRecentObject = Open-ExcelPackage -Path $mssqlCMSFile -Verbose

The final block refactored:

$sqlVersionRowsArray = [System.Collections.Generic.List[object]]::new()
$rows = Foreach ($version in $sqlVersions) {
    $sqlVersionRows = Import-Excel -Path $sqlVersionFile -WorksheetName $version -AsDate 'Release Date'  
    $sqlVersionRows | Add-Member -MemberType NoteProperty -Name Version -Value $version        
    [pscustomobject]@{
        'Version'                           = $version
        'Build Number'                      = $sqlVersionRows.'Build Number'.Get(1)
        'KB Number'                         = $sqlVersionRows.'KB Number'.Get(1)
        'KB URL'                            = $sqlVersionRows.'KB URL'.Get(1)
        'Release Date'                      = $sqlVersionRows.'Release Date'.Get(1)
        'Service Pack Level'                = $sqlVersionRows.'Service Pack Level'.Get(1)
        'Cumulative Update or Security ID'  = $sqlVersionRows.'Cumulative Update or Security ID'.Get(1)
        'Servicing Model'                   = $sqlVersionRows.'Servicing Model'.Get(1)
        'Notes'                             = $sqlVersionRows.'Notes'.Get(1)
    }
	
    $sqlVersionRowsArray.Add($sqlVersionRows.Get(1))
}
$rows | Export-Excel -Path $mssqlCMSFile -WorksheetName 'SQL Versions'

[Shashahan]

Thank you very much for your answer.
Unfortunately I still receive the same error

Exception calling "Save" with "0" argument(s): "Error saving file C:\Users\Username\Documents\temp\Mssql_instance_list.xlsx"
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.9\Public\Export-Excel.ps1:686 char:20
+             else { $pkg.Save() }
+                    ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

I also cleaned up the listing a bit:

$rows = [System.Collections.Generic.List[object]]::new()
$rows = Foreach ($version in $sqlVersions) {
    $sqlVersionRows = Import-Excel -Path $sqlVersionFile -WorksheetName $version -AsDate 'Release Date'      
    [pscustomobject]@{
        'Version'                           = $version
        'Build Number'                      = $sqlVersionRows.'Build Number'.Get(1)
        'KB Number'                         = $sqlVersionRows.'KB Number'.Get(1)
        'KB URL'                            = $sqlVersionRows.'KB URL'.Get(1)
        'Release Date'                      = $sqlVersionRows.'Release Date'.Get(1)
        'Service Pack Level'                = $sqlVersionRows.'Service Pack Level'.Get(1)
        'Cumulative Update or Security ID'  = $sqlVersionRows.'Cumulative Update or Security ID'.Get(1)
        'Servicing Model'                   = $sqlVersionRows.'Servicing Model'.Get(1)
        'Notes'                             = $sqlVersionRows.'Notes'.Get(1)
    }
}

$rows | Format-Table -AutoSize # Just checking the data is there
$rows | Export-Excel -Path $mssqlCMSFile -WorksheetName 'SQL Versions'

[scriptingstudio]

it works just fine
Mssql_instance_list.xlsx
test code:

$sqlVersions = '2008 R2', '2012', '2014', '2016', '2017', '2019', '2022'
$sqlVersionsUrl = "https://aka.ms/sqlserverbuilds"
$sqlVersionFile = 'SqlUpates.xlsx'
Invoke-WebRequest $sqlVersionsUrl -OutFile $sqlVersionFile
$rows = Foreach ($version in $sqlVersions) {
    $sqlVersionRows = Import-Excel -Path $sqlVersionFile -WorksheetName $version -AsDate 'Release Date'      
    [pscustomobject]@{
        'Version'                           = $version
        'Build Number'                      = $sqlVersionRows.'Build Number'.Get(1)
        'KB Number'                         = $sqlVersionRows.'KB Number'.Get(1)
        'KB URL'                            = $sqlVersionRows.'KB URL'.Get(1)
        'Release Date'                      = $sqlVersionRows.'Release Date'.Get(1)
        'Service Pack Level'                = $sqlVersionRows.'Service Pack Level'.Get(1)
        'Cumulative Update or Security ID'  = $sqlVersionRows.'Cumulative Update or Security ID'.Get(1)
        'Servicing Model'                   = $sqlVersionRows.'Servicing Model'.Get(1)
        'Notes'                             = $sqlVersionRows.'Notes'.Get(1)
    }
}
$mssqlCMSFile = "Mssql_instance_list.xlsx"
$rows | Export-Excel -Path $mssqlCMSFile -WorksheetName 'SQL Versions'

It is probably something wrong with your module setup

UPDATE

Try different file locations (not TEMP)