Powershell unable to add value to Excel cell

user_2591131 0
Viewed 456

I have a Powershell script which inserts values into multiple rows, columns and sheets of an Excel file.

The script works fine, except for 1 column. The cells within the problem column are "select" cells, they have a small set of values to select from "update", "create" and "delete". When the script runs no errors are reported, the cells simply remain empty.

This is the code to insert the values:

$Worksheet.Cells.Item($Row, 8).Value2 = $Obj.Value

There are several other "select" cells within the sheets and all the others work fine, just column "8" within the 1 sheet is a problem. I've reviewed the $Obj.Value and the values match (from what I can tell).

Is there another way to set the cell value? What I've tried so far:

$Worksheet.Cells.Item($Row, 8) = $Obj.Value  # Same result as .Value2

$Worksheet.Cells.Item($Row, 8).Text = $Obj.Value # Throws an Exception: "Unable to set the Text property of the Range class"

Is there another way to set the value? Is there something I need to check within the Excel file?

Also, when I open the file in Excel, I can manually edit the cell with no noticable issues - I am currently lost as to why the script can't maintain the cells.

Solution

Interferring VBA code.

powershell-4.0 excel powershell
1 Answers

I finally found the cause of the issue, there was a hidden VBA function which reacted on an update to the column, which seemed not to affect user input, however it is what was stopping the script from working.

After modifying the VBA code, the script works fine again.

Thanks everyone for your comments/feedback.

user_25911310
Related