I know this question is a bit old, but I ran into it while looking for other Excel/PowerShell help and had recently found one potential fix. A two line change may actually speed up the entire process significantly.
Change from:
#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)
To:
#loop through the worksheets in the current workbook
$sheetCount = $wb.Sheets.Count
for ($i = 1; $i -le $sheetCount; $i++)
The reason lies in how the COM object works internally, which I can't fully explain myself. This StackOverflow question This StackOverflow question briefly reviews why not to use 2 dots when referencing COM objects. Second, this blog post identifies this specific change as improving performance from 14 minutes to 10s. I haven't validated myself, but I would expect some improvement. Hope that helps... even if it is over a year late.
I know this question is a bit old, but I ran into it while looking for other Excel/PowerShell help and had recently found one potential fix. A two line change may actually speed up the entire process significantly.
Change from:
#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)
To:
#loop through the worksheets in the current workbook
$sheetCount = $wb.Sheets.Count
for ($i = 1; $i -le $sheetCount; $i++)
The reason lies in how the COM object works internally, which I can't fully explain myself. This StackOverflow question briefly reviews why not to use 2 dots when referencing COM objects. Second, this blog post identifies this specific change as improving performance from 14 minutes to 10s. I haven't validated myself, but I would expect some improvement. Hope that helps... even if it is over a year late.
I know this question is a bit old, but I ran into it while looking for other Excel/PowerShell help and had recently found one potential fix. A two line change may actually speed up the entire process significantly.
Change from:
#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)
To:
#loop through the worksheets in the current workbook
$sheetCount = $wb.Sheets.Count
for ($i = 1; $i -le $sheetCount; $i++)
The reason lies in how the COM object works internally, which I can't fully explain myself. This StackOverflow question briefly reviews why not to use 2 dots when referencing COM objects. Second, this blog post identifies this specific change as improving performance from 14 minutes to 10s. I haven't validated myself, but I would expect some improvement. Hope that helps... even if it is over a year late.
I know this question is a bit old, but I ran into it while looking for other Excel/PowerShell help and had recently found one potential fix. A two line change may actually speed up the entire process significantly.
Change from:
#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)
To:
#loop through the worksheets in the current workbook
$sheetCount = $wb.Sheets.Count
for ($i = 1; $i -le $sheetCount; $i++)
The reason lies in how the COM object works internally, which I can't fully explain myself. This StackOverflow question briefly reviews why not to use 2 dots when referencing COM objects. Second, this blog post identifies this specific change as improving performance from 14 minutes to 10s. I haven't validated myself, but I would expect some improvement. Hope that helps... even if it is over a year late.