Excel Manipulation using Powershell

Question

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.

Why powershell? Excel has VBA built-in which is likely more efficient and also easier to just start with a button from within the file itself. — jkpieterse
– jkpieterse, Commented Aug 29, 2019 at 12:35
In addition, Excel has a lot of data manipulation capabilities that do not require any programming. — jkpieterse
– jkpieterse, Commented Aug 29, 2019 at 12:36
Powershell seems easier to me and at the end of the day Powershell can access Microsoft's Access, that way I can use one tool to manipulate two. That's why the first thing I asked was exactly that "why PowerShell and not Excel" and the answer was pretty clear "Powershell so I can later solve more complex problems" — borys
– borys, Commented Aug 29, 2019 at 12:44
@jkpieterse That's not helpful. We don't know OP's requirements. And the entirety of the VBA Excel API is available to PowerShell, plus all of .NET and other PowerShell cmdlets. — Lews Therin
– Lews Therin, Commented Aug 29, 2019 at 12:47
@LewsTherin Requirements? I just need to split my data to Imports and exports and then fill in the gaps with 0 values (the gaps can be found where Place1 is not present both as a Destination and Origin, which in lamest terms means that the count of rows/records for Destination and Origin must be equal) After that I'll try to connect with a Microsoft Access to automatically upload my results. — borys
– borys, Commented Aug 29, 2019 at 12:53

Don Cruickshank · Accepted Answer · 2019-08-29 13:00:09Z

1

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
}

edited Aug 29, 2019 at 13:00

answered Aug 29, 2019 at 12:50

Don Cruickshank

6,0086 gold badges53 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lews Therin Over a year ago

It could be this or that the value in $Filepath is wrong. I tested OP's code without any problem calling Select(), so either one of these issues seems most likely.

Lews Therin Over a year ago

Great answer. You can check that the $Filepath is a valid Windows path by using Test-Path.

borys Over a year ago

I do the same work for a lot of files, so I decided to use a prompt each time I wanted to do the report, I mean...c'mon

borys Over a year ago

Nevertheless thank you for pointing out. I used $ws1 = $Workbook.Sheets.Item(1) to fix this issue. It was pretty easy once you mentioned the name was incorrect.

Collectives™ on Stack Overflow

Excel Manipulation using Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related