ImportExcel for Dataset with Multiple tables

Question

I need to be able to create an Excel document with multiple tabs for a dataset with multiple results\data tables. I have a stored procedure with 3 selects in them, fairly simple ones pulling data from the sys tables in SQL Server.

Below is my script where I am assuming there will be 5 tables, if there are more then it will be missed and if there are less and then the other tabs will be empty.

What's the best way to loop through the dataset and create the Excel document?

$SQLServer = '.\SQLEXPRESS';
$Database = 'AdventureWorks';
$sqlCommand = "SELECT 'Tables' AS DataSetName, * FROM sys.tables

SELECT 'Columns' AS DataSetName, * FROM sys.columns";

$connectionString = "Server=$SQLServer;Database=$Database;Integrated 
Security=true"
$connection = New-Object 
System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.Sqlclient.Sqlcommand($sqlCommand, 
$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.SqlDataAdapter $command 
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) > null
$connection.Close()

foreach($table in $dataset.Tables)
{
    Write-Host $table.TableName
    if ($($table.TableName) -eq 'table'){$Table1 = $table}
    if ($($table.TableName) -eq 'table1'){$Table2 = $table}
    if ($($table.TableName) -eq 'table2'){$Table3 = $table}
    if ($($table.TableName) -eq 'table3'){$Table4 = $table}
    if ($($table.TableName) -eq 'table4'){$Table5 = $table}
}

$DataToGather = @{
    table = {$Table1}
    table1 = {$Table2}
    table2 = {$Table3}
    table3 = {$Table4}
    table4 = {$Table5}
}

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

$DataToGather.Clear()

Answer 1

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()