topic In any case, if you are in Product Forum

Excel Paste Special

NiallFee — Tue, 18 Jul 2017 19:18:00 GMT

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

The Worksheet Object has a

Anton__Hosang — Tue, 18 Jul 2017 20:49:00 GMT

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

In any case, if you are

Anton__Hosang — Tue, 18 Jul 2017 21:16:00 GMT

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

Thanks very much for your

NiallFee — Wed, 19 Jul 2017 13:27:00 GMT

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!