CODEWITHSUNDEEP
excelpowershell
borys
borys

Reputation: 13

Excel Manipulation using Powershell

I have been working with Excel for the past month or so, mainly doing reports for a research project, and recently I realized that I could write a powershell script that can speed up my work.

Example Table: 

+-------+-----------+--------+-------------+------+------+------+------+------+------+------+------+
| text1 | Direction | Origin | Destination | num1 | num2 | num3 | num4 | num5 | num6 | num7 | num8 |
+-------+-----------+--------+-------------+------+------+------+------+------+------+------+------+
| Test1 | Import    | Place3 | Place1      | x1   | x4   | x7   | x10  | x13  | x16  | x19  | x22  |
+-------+-----------+--------+-------------+------+------+------+------+------+------+------+------+
| Test2 | Export    | Place4 | Place2      | x2   | x5   | x8   | x11  | x14  | x17  | x20  | x23  |
+-------+-----------+--------+-------------+------+------+------+------+------+------+------+------+
| Test3 | Import    | Place5 | Place1      | x3   | x6   | x9   | x12  | x15  | x18  | x21  | x24  |
+-------+-----------+--------+-------------+------+------+------+------+------+------+------+------+

So, what I do is that I open a xlsx file, that contains rows filled with package delivery details, seperate imports from exports and then compare Imports with exports and if there are missing desinations (more on that later) I will add the destinations as values and then assign "0" to their individual columns.

Right now I've written the following lines and I don't know where to go from there:

# Create Excel Object
$Excel = New-Object -ComObject Excel.Application

# Prompt for file path
$Filepath = Read-Host -Prompt 'Where can I find your file? (Path\to\File.xlsx)'

# Open File from inside PowerShell
$Workbook = $Excel.Workbooks.Open($Filepath)

# Use two variables to assign two sheets
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}
$ws2 = $wb.worksheets | where {$_.name -eq "sheet2"}

# Assign used range to a variable
$mainRng = $ws1.usedRange

# Select Range
$mainRng.Select()

#Find a test value
$ValueSearch = $mainRng.Find("Import")

The reason I'm now stuck is because I get Null values when I'm selecting a range.

Error Message:

  • $mainRng.Select()
  • + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

How can I fix that so I can proceed with my development? Because after that I will loop through every row and, using a conditional, I will seperate Import from Export.

Upvotes: 1

Views: 431

Answers (1)

Don Cruickshank
Don Cruickshank

Reputation: 5928

On possible problem is that the Excel file you're processing does not have worksheets with the labels sheet1 and sheet2.

Even if there are no used cells in a worksheet, your code will select the range A1.

However if, for example, sheet2 isn't defined in your worksheet then in your code the variables $ws2 and $mainRng will both be $null. PowerShell is actually fine with this but the script will fail at the point that you try to call the Select function.

Or, as Lews Therin points out in the comments, the filename that you've provided isn't correct.

Either way around, the solution is to start checking your variables to see if they're defined so that you can detect these problems earlier and warn the user appropriately. E.g.:

if (!$ws1) {
    Write-Host "WorkBook does not contain the worksheet 'sheet2'"
    exit
}

Upvotes: 1

Related Questions