Excel Paste Special

[NiallFee]

Hi I am trying to amend the 'Copy and Paste Worksheet Range' action in the MS Excel VBO so that it will paste values rather than formulae. I am using the info at the following link for reference: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.pastespecial.aspx I can not figure out how to use the XlPasteType constants to specify that I want it to use xlPasteValues. I tried changing the line in the code from sheet.Paste() to sheet.PasteSpecial(XlPasteType.xlPasteValues) but it doesn't work. Can anyone advise? Thanks

[Anton__Hosang]

The Worksheet Object has a different PasteSpecial parameter list to the Range.PasteSpecial method. You really want the Range.PasteSpecial method for your purpose.

[Anton__Hosang]

In any case, if you are copying values only, then try to avoid using the clipboard. There is no point messing with the clipboard if any other developer has not cleared it etc. It also has no advantage over the below code: Dim sheet as Object sheet = GetWorkSheet(handle, workbook, worksheet) 'declare params Dim arr as Object(,) arr = sheet.Range(srcRangeString).Value2 'declare param srcRangeString Dim destSheet as Object destSheet = GetWorksheet(destHandle, destWorkbook, destWorksheet) 'declare params Dim destRange as Object destRange = destSheet.Range(startCell).Resize(arr.GetUpperbound(0), arr.getUpperBound(1)) 'declare param startCell to represent the top left destination cell destRange.Value = arr 'one-stop value transfer from array to range If you wanted to follow the code in the Excel VBO, then you could do instead: ..... ..... destination.PasteSpecial(-4163) 'xlPasteValues

[NiallFee]

Thanks very much for your help. I hadn't noticed the difference between the Worksheet and Range methods. I also wasn't familiar with the array functionality you laid out. Thanks again!