Powershell: Excel combine worksheets into a single worksheet

Question

I have some web script that I've adapted to run 7 T-SQL queries and output the results into 1 Excel workbook, one worksheet per query. I've just been asked if I can combine all 7 worksheets into one.

Here's my sample code which does copy a worksheet, however the entire column(s) are selected instead of just the UsedData. Also, the first worksheet's data on the destination worksheet is replaced by the second worksheets data.

Questions: Would it be simpler to get Powershell to output the 7 queries into One Excel Worksheet separated by two blank rows? Or modify the existing Powershell script to create the 7 worksheets then combine them into one?

Code is not pretty! I also have been really lost using $Excel = New-Object -ComObject excel.application followed by $Excel | Get-Member to explore how to get PowerShell to work with Excel. References on MSDN are usually for VB or C languages and I can't translate that into PowerShell.

--Edit, add code that stores 7 Query results in an array and outputs to the console. The data is correct but I'm just unsure how to approach piping that data into a single Excel Worksheet.

$docs = "C:\Temp\SQL\test.xlsx"
If (Test-Path $docs){Remove-Item $docs}
Function First-Query {
param([string[]]$queries)
$xlsObj = New-Object -ComObject Excel.Application
$xlsObj.DisplayAlerts = $false
## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0
$xlsWb = $xlsobj.Workbooks.Add(1)
$xlsSh = $xlsWb.Worksheets.Add([System.Reflection.Missing]::Value, $xlsWb.Worksheets.Item($xlsWb.Worksheets.Coun))
$xlsSh.Name = 'Test'
for ($i = 0; $i -lt $queries.Count; $i++){
$query = $queries[$i]
$SQLServer = 'Server'
$Database = 'DataBase'
## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd;
$tables = New-Object System.Data.DataSet;
$SqlAdapter.Fill($tables)
$TableArray = @($tables)
$SqlConnection.Close()
$DataSetTable = $TableArray.Tables[0]
}#End For Loop
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames){
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
}
## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows){
foreach ($Coln in $getColumnNames){
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rec.$($Coln.ColumnName).ToString()
$ColData++
}
$rowData++; $ColData = 1
}
## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange
$xlsRng.EntireColumn.AutoFit() | Out-Null
#End for loop.
#Delete unwanted Sheet1.
$xlsWb.Sheets.Item('Sheet1').Delete()
#Set Monday to Active Sheet upon opening Workbook.
$xlsWb.Sheets.Item('Monday').Activate()
## ---------- Saving file and Terminating Excel Application ---------- ##
$xlsFile = "C:\Temp\SQL\test.xlsx"
$xlsObj.ActiveWorkbook.SaveAs($xlsFile) | Out-Null
$xlsObj.Quit()
## - End of Script - ##
start-sleep 2
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsSh)) {'cleanup xlsSh'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsWb)) {'cleanup xlsWb'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)) {'cleanup xlsObj'}
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null
}#End Function
$queries = @()
$queries += @'
SELECT DISTINCT
'@
First-Query -queries $queries

Answer 1

Not sure I really understand what your problem is but below is a "template" that might help you to do what you want. It shows you how you can create sheets and handle them. You'll have to fill the blanks (see the commented section, where you have to call your query function).

param (
    [string] $ExcelFile = (Read-Host "Enter full path for Excel file")
)

try
{
    $Error.Clear()

    # http://support.microsoft.com/kb/320369
    [System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo] "en-US"

    Push-Location
    $scriptPath = Split-Path -parent $MyInvocation.MyCommand.Path

    Set-Location $scriptPath

    $Excel = New-Object -comobject Excel.Application
    $Excel.Visible = $True

    $WorksheetCount = 7

    $Workbook = $Excel.Workbooks.Add()
    $Workbook.Title = 'My Workbook'


    $weekdays = @("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")


    ($WorksheetCount - 1)..0 | %{

        $sheet = $Excel.Worksheets.Add()
        $sheet.Name = $weekdays[$_]

        # $dataTable = Execute-Your-Query-Here-Returning-A-Data-Table

        # $x = 0
        # $dataTable | %{
            # $sheet.cells.item($x, 1) =  ...
            # $sheet.cells.item($x, 2) =  ...
            # $x++
        # }
    }   

    $excel.ActiveWorkbook.SaveAs("$ExcelFile")
 }

catch
{
    "$($MyInvocation.InvocationName): $Error"
}

finally
{
    Pop-Location

    $Excel.Quit()
    $Excel = $null

    [gc]::collect()
    [gc]::WaitForPendingFinalizers()
}