Open Files From Array

Question

Post reply

Open Files From Array

RonMexico

Hall of Fame

Points: 3474

August 6, 2019 at 7:33 pm

#3669264

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?

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

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

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: 3474 More actions · Answer 2

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
}