$worksheet
This is something I rediscovered this morning and thought it was worth a quick blog. How often have you wanted to do something and you google lots of examples but when you read them you find that the code shown uses “constants” which are listed as the well known constant name, e.g. I wanted to paste the values from some cells in a worksheet to another cell in an Excel workbook.
Googling revealed examples but most of them refer to using an excel constant in this case $excel.pasteSpecial(xlPasteValues) but of course that code doesn’t work in powershell. I initially solved it but finding a post that told me the value of xlPasteValues was in fact -4163. So:
$range.PasteSpecial(–4163) | Out-Null
should do the trick but how memorable is that!
Yesterday when I was googling this I didn’t find any useful links but this morning researching for this post I hit upon this one which is actually pretty useful as it tells you all the values of the xlPasteTypes :
https://msdn.microsoft.com/en-us/library/office/ff837425.aspx
Anyway the tip I’m blogging about here is how to get these values into your script in a readable and memorable way. It’s something I’d already done a while back in my Excel functions ( not sure not if I ever got around to publishing them, I should really if I haven’t as I use them all the time, in fact so much I converted them into a module so if people – like anyone reads this – are interested let me know and I’ll spend some time publishing them, maybe a function at a time).
Anyway in my excel function module I use these lines of code , the last one being the new one added today:
$lineStyles = “microsoft.office.interop.excel.xlLineStyle” -as [type]
$colorIndexs = “microsoft.office.interop.excel.xlColorIndex” -as [type]
$borderWeights = “microsoft.office.interop.excel.xlBorderWeight” -as [type]
$chartTypes = “microsoft.office.interop.excel.xlChartType” -as [type]
$pasteTypes = “microsoft.Office.Interop.Excel.XlPasteType” -as [type]
# Enumeration types are used to tell Excel which values are allowed
# for specific types of options. As an example, xlLineStyle enumeration
# is used to determine the kind of line to draw: double, dashed, and so on.
# These enumeration values are documented on MSDN.
# To make the code easier to read, I created shortcut aliases for each
# of the four enumeration types we will be using.
# Essentially, we’re casting a string that represents the name of the
# enumeration to a [type]. This technique is actually a pretty cool trick:
# http://technet.microsoft.com/en-us/magazine/2009.01.heyscriptingguy.aspx
I even wrote some lines in my code to remind me what these were and how to use them – but I’d forgotten all about them.
# added “Microsoft.Office.Interop.Excel” in as some sites implied that you have to have it
# but my code to center a cell worked fine without it – but when you search for help on Excel
# you often see people using the constants so if by loading this I can use the constants too making the code look cleaner.
# e.g. $sheet.Cells.item(1,1).HorizontalAlignment = $xlConstants::xlCenter
# rather than $sheet.Cells.item(1,1).HorizontalAlignment = -4108
# $($xlConstants::xlCenter).value__ returns -4108
for this example If I want to cut and paste the values from one set of cells I do the following:
$workbook = $(New-Object -comobject excel.application)
$workbook.workbooks.Open(“c:\Temp\test123.xlsx”) | Out-Null
$worksheet = $workbook.activesheet
$workbook.visible = $true
$cellReference = “A1:A4”
$range = $worksheet.range($cellReference)
$range.Copy() | out-null
$range = $worksheet.range(“B1”)
#$range.PasteSpecial(-4163) | Out-Null # instead of this meaningless bit of code use the constants
$range.PasteSpecial([Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteValues) | Out-Null
If you use Powergui you can expand the variables and see what’s inside them 🙂
