adding new sheet in Excel with Powershell

Question

How does one specify the options? This works $workbook.worksheets.Add() but I want to specify a worksheet to add this new one after. That's the second of four possible options:.Add(Before, After, Count, Type). I can't seem to figure out what the proper syntax in Powershell is. If I get it wrong, there's no error, but the worksheet is not created. For example, $workbook.worksheets.Add(,$lastSheet) does not work. $lastSheet of course is a sheet object. I instantiated that with $sheet = $workbook.ActiveSheet $lastSheet = $sheet

I tried to figure this out once, and eventually gave up and just created the sheet at the default location and moved it later. — TheMadTechnician
– TheMadTechnician, Commented Sep 8, 2016 at 23:33

Roman · Accepted Answer · 2016-09-08 23:46:31Z

3

$workbook.worksheets.Add([System.Reflection.Missing]::Value,$lastsheet)

Apparently the [System.Reflection.Missing]::Value can be used to represent a value in this kind of construction because a null is not allowed.

answered Sep 8, 2016 at 23:46

Roman

5513 gold badges7 silver badges24 bronze badges

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

1 Comment

Roman Over a year ago

Kind of janky. I wonder if there is a better way to do this kind of stuff with Office applications. Write it in VBA and then call that somehow with Powershell? I don't know if that would flow anyway.

Collectives™ on Stack Overflow

adding new sheet in Excel with Powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related