Export results onto an existing file in powershell

Question

I got a query from a database which needs to be exported onto an existing xlsm file. I have put some code around the process but unable to get the final output. The output of the query will go into row 2 of the existing file.

Here is what I have so far, here I will need the array held in query_result placed in the xlsm file.

$query_result = invoke-sqlcmd -ServerInstance 'server1' -Database 'DB' -query 'Exec spGetResult'  
Add-Type -AssemblyName Microsoft.Office.Interop.Excel  
            $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled  
            $XL = New-Object -comobject Excel.Application  
            $FilePath = "V:\$fileName.xlsm"  
            $workbook = $XL.Workbooks.Open($FilePath)  
            $WS = $workbook.Worksheets("Sheet1")  
            $XL.DisplayAlerts = $False  
            $WB.SaveAs($FilePath,$xlFixedFormat)  
            $WB.Close  
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($XL) | Out-Null  
              

Answer 1

Hi，

Assuming $query_result has the properties reference, name, state and lastupdated, you may write the file like this

Add-Type -AssemblyName Microsoft.Office.Interop.Excel  
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled              
$XL = New-Object -comobject Excel.Application  
$FilePath = "V:\$fileName.xlsm"  
$WB = $XL.Workbooks.Open($FilePath)  
$WS = $workbook.Worksheets("Sheet1")  
$XL.DisplayAlerts = $False  
$startrow = 2  
for($i=0;$i -lt $objs.Count;$i++){  
    $ws.Cells.Item($startrow+$i,1) = $query_result[$i].Reference  
    $ws.Cells.Item($startrow+$i,2) = $query_result[$i].Name  
    $ws.Cells.Item($startrow+$i,3) = $query_result[$i].State  
    $ws.Cells.Item($startrow+$i,4) = $query_result[$i].LastUpdated  
}  
$WB.SaveAs($FilePath,$xlFixedFormat)  
$WB.Close()  
$XL.Quit()               
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($XL) | Out-Null  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.