Open Files From Array

  • RonMexico

    Hall of Fame

    Points: 3251

    I'm creating an array containing paths for files that I need to open from a SQL query using the following

    $QueryResult = Invoke-Sqlcmd -Query "SELECT FilePath from Table" -ServerInstance "SQLSRVDEV"
    return $QueryResult

    That works fine. I can also specify to open an individual file using

    $excel = New-Object -comobject Excel.Application

    #open file
    $FilePath = 'C:\temp\File1.xlsx'
    $workbook = $excel.Workbooks.Open($FilePath)

    However, I cannot figure out how to cursor through the array to open each file returned by the initial SQL query. Here is what I have for that

    $QueryResult = Invoke-Sqlcmd -Query "SELECT FilePath from Table" -ServerInstance "SQLSRVDEV"

    foreach ($element in $QueryResult) {
    $excel = New-Object -comobject Excel.Application

    #open file
    $FilePath = $element
    $workbook = $excel.Workbooks.Open($FilePath)

    #make it visible (just to check what is happening)
    $excel.Visible = $true
    }

    The error I get is

    Sorry, we couldn't find System.Data.DataRow. Is it possible it was moved, renamed or deleted?
    At line:8 char:1
    + $workbook = $excel.Workbooks.Open($element)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

    Any suggestions?

  • ZZartin

    SSC-Dedicated

    Points: 30338

    Your $excel.Workbooks.Open($element) is just converting the element with a simple tostring which just returns System.Data.DataRow

     

    Try $FilePath = $element.FilePath

     

     

  • RonMexico

    Hall of Fame

    Points: 3251

    Thanks for the tip ZZartin. That fixed my problem. I didn't mention that each Excel file opened will run its own macro which leads me to my next issue. If one of the macros has an error then it will open Excel with a pop up giving the reason for the error. I would like to recognize that it had an error without requiring manual intervention to close out the pop up. Ideally Powershell would ignore the pop and perform some kind of error check at the end of each file opened. If an error is detected, then it will not update the EndDate and will continue to process any remaining files. Is this possible? Here is what I have so far

    $QueryResult = Invoke-Sqlcmd -Query "SELECT ID, FilePath from Table" -ServerInstance "SQLSRVDEV"

    foreach ($element in $QueryResult) {
    $CurrentStartTime = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date)
    $FilePath = $element.FilePath
    $FileID = $element.ID -as [int]

    Invoke-Sqlcmd -Query "update Table set StartDate='$CurrentStartTime' where ID='$FileID'" -ServerInstance "SQLSRVDEV"
    $excel = New-Object -comobject Excel.Application

    #open file
    $FilePath = $element.FilePath
    $workbook = $excel.Workbooks.Open($FilePath)

    $excel.Visible = $false
    $excel.quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

    $CurrentEndTime = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date)
    Invoke-Sqlcmd -Query "update Table set EndDate='$CurrentEndTime' where ID='$FileID'" -ServerInstance "SQLSRVDEV"
    $excel = New-Object -comobject Excel.Application
    }

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply