Forum Post: Excel and Powershell Question

Hello, So I am trying to help write a script that goes through different excel workbooks and runs a macro in order to normalize data. But I am running into a set of errors that I don’t understand. I thought all of the expressions were defined and don’t quite understand what is happening. The code so far: #Step 1 Macro $report=’C:Usersgconway2DesktopAllServersTest.csv’ $newreport= ‚C:Usersgconway2DesktopStep 1 – Data Normalization Workbook(Test).xlsm’ $excel=New-Object -ComObject excel.application $excel.visible=$True $workbookreport=$excel.Workbooks.open($report) $NormalizationWB=$excel.Workbooks.open($newreport) $worksheetreport=$workbookreport.WorkSheets.item(„AllServersTest”) $worksheetreport.activate() $range=$worksheetreport.Range(„(A2):(J:J)”) $range.Copy() | Out-Null $NormalizationWSheet = $NormalizationWB.Worksheets.item(„Data”) $NormalizationWSheet.activate() $rangenewreport=$NormalizationWSheet.Range(„A2:(J:J)”) $NormalizationWSheet.Paste($rangenewreport) #$NormalizationWSheet.Save() #Does not save but why??? NO CLUE $excel.Run(„CSVNormalization”) #Step 2 **Manual** Classification of Vulnerabilities #Step 3 $lastrow=($NormalizationWSheet.Rows.Count – 1) # Used to determine the last row in the WS (the last row with valid data) $range3=$NormalizationWSheet.Range(„A2,A32549:O2,O32549”) $range3.Copy() | Out-Null $Calculation=’C:Usersgconway2DesktopStep 2 (All) Calculation Workbook.xlx’ $CalculationWB = $excel.WorkBook.open($Calculation) $CalculationWS = $CalculationWB.WorkSheets.item(‚RawData’) $CalculationWS.activate() $calculationRange = $CalculationWS.Range(„A2:O,O)”) $CalculationWS.Paste($calculationRange) ___________________________________________________________________________ Errors : This action won’t work on multiple selections. At line:28 char:1 + $range3.Copy() | Out-Null + ~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException You cannot call a method on a null-valued expression. At line:30 char:1 + $CalculationWB = $excel.WorkBook.open($Calculation) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At line:31 char:1 + $CalculationWS = $CalculationWB.WorkSheets.item(‚RawData’) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At line:32 char:1 + $CalculationWS.activate() + ~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At line:33 char:1 + $calculationRange = $CalculationWS.Range(„A2:O,O)”) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At line:34 char:1 + $CalculationWS.Paste($calculationRange) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull

GD Star Rating
loading...