ImportExcel for Dataset with Multiple tables
If the Excel sheet names should retain the original TableNames this could do:
(can't test myself)
The code contains two commented out alternatives.

# your code upto $connection.Close() ...

$DataToGather = @{}  
foreach ($table in $dataset.Tables) {
    Write-Host $table.TableName
    Add-Member -InputObject $DataToGather -Name $table.TableName -Value $table 
#   Add-Member -InputObject $DataToGather -Name $table.TableName -Value $dataset.Tables['$($table.TableName)']
#   $DataToGather.Add($table.TableName,$table)
}

Export-MultipleExcelSheets -Path 'C:\Temp\Test.xlsx' -InfoMap $DataToGather -Show -AutoSize 
#$DataToGather

$DataToGather.Clear()