Powershell Get Excel cell information

Greeting,

How can I use powershell to open an Excel file and find a empty cell?

Thanks

Usera

July 22nd, 2015 5:13pm

A quick search of TechNet for 'powershell read excel file' returns a number of examples of how to read an Excel spreadsheet.  Many also have different examples of reading the data in different ways.

Pick some examples and give it a try.  The best way to learn is attempt things yourself and learn from your mistakes. 

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 5:55pm

I can just type it in from memory which is what you get when you spend time testing and trying things.

$xl=New-Object -Com Excel.Aplication
$xl.Workbooks.Add()
$xl.ActiveSheet.Cells.Item(1,1) = 'Hello World
$xl.ActiveWorkbook.SaveAs('myboook.xlsx')
$xl.Quit()

July 22nd, 2015 6:17pm

Find first empty cell in sheet.

foreach($cell in $xl.ActiveSheet.Cells){if($cell.Formula -eq ''){$_;break}}

$cell is now the first empty cell.

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 6:24pm

Find first empty cell in sheet.

foreach($cell in $xl.ActiveSheet.Cells){if($cell.Formula -eq ''){$cell;break}}

$cell is now the first empty cell.

July 22nd, 2015 10:18pm
Did you mean {$cell;break}?
Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 8:03am

Usera, what do you mean by "find empty cell"? Please elaborate.


July 23rd, 2015 8:15am
Did you mean {$cell;break}?

It ends the loop on the first empty cell.

The first empty cell is the first cell with no formula or text.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 10:38am
Break ends the loop. $_ serves no purpose.
July 23rd, 2015 11:48am
Break ends the loop. $_ serves no purpose.

It stops the loop at the first empty cell.  Now "$cell" is the first empty cell which is what was requested.

Have you even tried to run it,

Note the filter:

if($cell.Formula -eq ''){$_;break}}

It breaks on first empty cell leaving "$cell" set to that value.

This is a classic programing construct to seek over a collection for a specific item.  It is a fundamental programming pattern.

<over [collection]>{break on match}

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 11:57am
Yes, I ran it. Break works great and $cell holds the first empty cell as requested. $_ is empty and as far as I can tell, not needed.
July 23rd, 2015 12:11pm

Usera, what do you mean by "find empty cell"? Please elaborate.


Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 12:14pm

Usera, what do you mean by "find empty cell"? Please elaborate.


July 23rd, 2015 12:14pm
Sorry. I fixed that before but as happens here more often now, the edit just disappeared.  It might have something to do with the constant stream of "Internal Server error" messages we get.
Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 12:24pm
Yes, the forums could use an overhaul. Frequent internal server errors, outdated telerik editor and not very mobile friendly.
July 23rd, 2015 1:14pm

Hah! Mobile! No such thing here.

MSDN forums work much better.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 1:22pm
MSDN forums work mu
July 23rd, 2015 1:30pm

No. How do we get to that?

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 1:36pm

Change the URL to point at MSDN instead of TechNet:

MSDN version:

https://social.msdn.microsoft.com/profile/jrv/?ws=usercard-mini

TechNet version:

https://social.technet.microsoft.com/profile/jrv/?ws=usercard-mini

July 23rd, 2015 1:39pm

Nice.  Much cleaner. Is it only on the profile?

With HTML5 they should be be to do amazing things.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 1:42pm

Yeah, it's only profile changes AFAIK.

I only recently found out about it. My avatar image doesn't fit very well.

July 23rd, 2015 1:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics