-Apologies for the back and forth question!
I pieced together the following PowerShell script which runs two SQL queries, exports each query to a CSV file then moves the CSV files into an Excel workbook.
The code works as expected when the two CSV files are already created. But the script fails when it is run the first time when the CSV files get created.
Function Run-Query {
param([string[]]$queries,[string[]]$sheetnames,[string[]]$filenames)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = 0
$dest = $Excel.Workbooks.Add(1)
for ($i = 0; $i -lt $queries.Count; $i++){
$query = $queries[$i]
$sheetname = $sheetnames[$i]
$filename = $filenames[$i]
### SQL query results sent to Excel
$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 '$Table2':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\Organize\ExcelStuff\$sheetname.csv"
}#End For.
#Begin excel test, loop over each CSV.
$loopy = (Resolve-Path $filename).ProviderPath
$Book = $Excel.Workbooks.Open($loopy)
foreach ($item in $loopy){
$next = $Excel.workbooks.Open($item)
$next.ActiveSheet.Move($dest.ActiveSheet)
$xlsRng = $dest.ActiveSheet.UsedRange
$xlsRng.EntireColumn.AutoFit() | Out-Null
}# END ForEach
#$Excel.Visible = 1 #For debugging.
$dest.sheets.item('Sheet1').Delete()
$xlsFile = "C:\Scripts\MonthlyReboots.xlsx"
$Excel.ActiveWorkbook.SaveAs($xlsFile) | Out-Null
$Excel.Quit()
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($next)) {'cleanup xlsSh'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Book)) {'cleanup xlsWb'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)) {'cleanup xlsObj'}
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null
}#End Function
$queries = @()
$queries += @'
'@
$queries += @'
'@
$sheetnames = @('Cert','Prod')
$filenames = @(".\prod.csv", ".\cert.csv")
Run-Query -queries $queries -sheetnames $sheetnames -filenames $filenames
Sheets.Add After:=Sheets(Sheets.Count)
I've got the code to output multiple SQL queries into Excel, 1 query per worksheet however it takes 15 minutes to work. - user4317867