Struggling to get a grasp of automating inserting data in to Excel with Powershell. So far I have managed to get columns with the correct date inserted in to sheets that match our cluster names. I am now hoping to pick values from a named range in an existing spreadsheet and then past the values in to a cell.
I have been able to find lots of pieces around creating a range and naming it but nothing that I can really understand to help me address an existing range. My COM understanding is limited but I am slowly piecing bit together - suspect I am missing something fundamental.
I thought I was close with the following:
[void]$SrcWorkSheet.Range("ClusterName",11).Copy()
But this seems to return an error that suggests backward compatability woth 2007 is causing the issue.
Exception from HRESULT: 0x800A03EC
I am eventually hoping to replace "ClusterName" with a variable I am looping through so it is repeateable across multiple sheets.
If you need to copy just the 11th row in your range,
$SrcWorkSheet.Range("ClusterName").Rows(11).Copy()