Export results onto an existing file in powershell

MrFlinstone 501 Reputation points
2021-02-24T14:35:03.89+00:00

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  
              

71644-image.png

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,435 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 32,326 Reputation points Microsoft Vendor
    2021-02-25T08:39:36.393+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful