[iLogic] PasteSpecial to excel

[JoãoASilva]

Hello!

I'm trying to copy a range from one excel file to another, in the context of exporting the BOM to an excel template. (This is in iLogic, not straight VB Code)

I can manage to copy with:

xlWorksheet.Range("B3").PasteSpecial

But I can't seem to pass the arguments, it returns an error every time:

1. xlWorksheet.Range("B3").PasteSpecial(xlWorksheet.XlPasteType.xlPasteValues)
nor
2. xlWorksheet.Range("B3").PasteSpecial(Paste:= xlPasteValues,Operation:= xlPasteSpecialOperationNone,SkipBlanks:= False,Transpose:= False)
nor
3. xlWorksheet.Range("B3").PasteSpecial(xlPasteValues,xlPasteSpecialOperationNone,False,False)
nor
4. xlWorksheet.Range("B3").PasteSpecial(xlPasteValues)

Is there a way to copy only the values, so it matches the formating of the template file?

Thanks!

[Michael.Navara · Accepted solution]

When you use Excel API from different environment than Excel, you can't use enum values using its non-qualified names. In this case you have two possibilities.

1. Add reference to Excel API

2. Define your own enums with identical values. Recommended.

Here is the sample which show how to do it. 

Sub Main
	'Open workbook and init Excel application
	GoExcel.Open("C:\Path\To\Test.xlsx", "List1")
	Dim a1 = GoExcel.CellValue("A1")
	excelApp = GoExcel.Application
	'Get reference to active sheet
	Dim wSheet = excelApp.ActiveSheet

	'Select range to copy
	Call wSheet.Range("A1:B3").Copy

	'Paste special
	Call wSheet.Range("D1").PasteSpecial( _
		Paste :=XlPasteType.xlPasteValues, _
		Operation :=XlPasteSpecialOperation.xlPasteSpecialOperationNone, _
		SkipBlanks :=True, _
		Transpose :=False)

	'Cleanup
	GoExcel.Save
	GoExcel.Close

End Sub

Enum XlPasteType
	'Source: https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype
	xlPasteAll = -4104	'Everything will be pasted.
	xlPasteAllExceptBorders = 7	'Everything except borders will be pasted.
	xlPasteAllMergingConditionalFormats = 14	'Everything will be pasted And conditional formats will be merged.
	xlPasteAllUsingSourceTheme = 13	'Everything will be pasted using the source theme.
	xlPasteColumnWidths = 8	'Copied column width is pasted.
	xlPasteComments = -4144	'Comments are pasted.
	xlPasteFormats = -4122	'Copied source format is pasted.
	xlPasteFormulas = -4123	'Formulas are pasted.
	xlPasteFormulasAndNumberFormats = 11	'Formulas And Number formats are pasted.
	xlPasteValidation = 6	'Validations are pasted.
	xlPasteValues = -4163	'Values are pasted.
	xlPasteValuesAndNumberFormats = 12	'Values  And Number formats are pasted.
End Enum

Enum XlPasteSpecialOperation
	'Source: https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastespecialoperation
	xlPasteSpecialOperationAdd = 2	'Copied data will be added To the value In the destination cell.
	xlPasteSpecialOperationDivide = 5	'Copied data will divide the value In the destination cell.
	xlPasteSpecialOperationMultiply = 4	'Copied data will multiply the value In the destination cell.
	xlPasteSpecialOperationNone = -4142	'No calculation will be done in the paste operation.
	xlPasteSpecialOperationSubtract = 3	'Copied data will be subtracted from the value In the destination cell.
End Enum

[JoãoASilva]

Hi @Michael.Navara !

Thank you for the explanation, I did not know that.

I managed to solve it using your example:

xlPasteValues = -4163
xlWorksheet.Range("B3").PasteSpecial(xlPasteValues)

Thank you!