So, I have a PowerShell script that runs 4 different SQL queries whose results I want to save in an Excel file with 4 sheets. I’m utilizing dfinke’s ImportExcel module version 6.1.0 and I’m running into this error whenever it’s trying to write the datasets on the Excel file.
Exception calling "Save" with "0" argument(s): "Error saving file C:\Script\Output\Output.xlsx"The output would be an excel file with just the first sheet created, the other 3 were not written.
Script looks like this
$outputFile = "C:\Script\Output\Output.xlsx";
$logfile = "C:\Script\logs.txt";
try {
$ts = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
Write-Output "$ts >> Saving dataset 0 to $outputFile" >> $logfile
$DataSet.Tables[0] | Export-Excel -Path $outputFile -worksheetname "Candidates_Emails Sent" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[1] | Export-Excel -Path $outputFile -worksheetname "Candidates_No Emails Sent Yet" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[2] | Export-Excel -Path $outputFile -worksheetname "Candidates_Logged In Per Day" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[3] | Export-Excel -Path $outputFile -worksheetname "Candidates_Total Logins Per Day" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
} catch {
$mergeErr = $_.Exception.Message
} finally {
$ts = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
Write-Output "$ts >> START MERGE ERROR |$mergeErr| END MERGE ERROR" >> $logfile
}
Troubleshooting
- I know all 4 datasets are being pulled completely because I have logging info saying how many rows were retrieved
- The error mesage only appears when I run the script on PowerGUI Script Editor, but when I schedule it on Task Scheduler, the error message disappears but the output is still erroneous
- I know there are no issues with any of the datasets because I changed the writing order and it always writes the first sheet and ignores the rest
Solution
Failing to save the other sheets could be an access issue. Make sure the user account running the script has permissions to not only read and write but also MODIFY as this enables the user to edit the worksheet that was created, and add new sheets. To be sure, I’m giving full control.
Right click the output folder and click on Properties. On Security tab, add or edit the permissions of the affected user. Make sure o include Modify.
Now, you may still run into the same issue after doing this but what I found helpful, albeit a weird workaround, is to rename the sheets into something simpler. When it works, try running it again using the original sheet names you planned and see if it eliminates the issue.
See here I changed the sheet names to Sheet 1, 2, 3, and 4
try {
$ts = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
Write-Output "$ts >> Saving dataset 0 to $outputFile" >> $logfile
$DataSet.Tables[0] | Export-Excel -Path $outputFile -worksheetname "Sheet 1" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[1] | Export-Excel -Path $outputFile -worksheetname "Sheet 2" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[2] | Export-Excel -Path $outputFile -worksheetname "Sheet 3" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
$DataSet.Tables[3] | Export-Excel -Path $outputFile -worksheetname "Sheet 4" -AutoSize -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
} catch {
$mergeErr = $_.Exception.Message
} finally {
$ts = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
Write-Output "$ts >> START MERGE ERROR |$mergeErr| END MERGE ERROR" >> $logfile
}
If changing the sheet names fixes it, try rolling back to your preferred sheet names and see if it works. For some reason, it worked for me.
Now for the error message appearing on PowerGUI Script Editor but not the Task Scheduler. I’ve learned to just ignore it since the script is already working fine. 🤣
Hope this helps!
