Pasting Values in Excel

$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 🙂

xlPasteTypes

Advertisements

Opening a CSV file in Excel when you have used the pipe delimiter

Related to my last post I discovered that Excel does not always open a CSV file and correctly determine the column breaks when you used the pipe delimiter.    I have no idea why that’s the case but you can work around this.

If you find that when you open a csv file that has used the pipe delimiter all of the data is in column A, which is not what you wanted you can work around this using one of these two techniques:

  1. Open Excel and then use file open to load the file – this will force the text import wizard to popup and then you can specify the delimiter to use.
  2. Convert the file once it’s opened – see below

I suspect you are like me and forget the formatting used in the file and just double click the CSV file and get presented with a single column of data ARRRGHHHH!  No problem, Excel can do the conversion for you without having to close the file and start again.

  1. Select the column with all the data by clicking in the column header
  2. select the data tab on the tool bar ribbon
  3. Click Text to Columns

This will fire up the Convert to Text Columns Wizard and you can then specify the delimiter – job done 🙂