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!
João Silva
Mechanical Engineer
If what I said solved your problem, or answered your question, please use the ACCEPT AS SOLUTION or KUDOS buttons.
Or if it helped you, please hit "LIKE"
Inventor Professional 2023.3, Build 359
Solved! Go to Solution.
Solved by Michael.Navara. Go to 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
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!
João Silva
Mechanical Engineer
If what I said solved your problem, or answered your question, please use the ACCEPT AS SOLUTION or KUDOS buttons.
Or if it helped you, please hit "LIKE"
Inventor Professional 2023.3, Build 359
Can't find what you're looking for? Ask the community or share your knowledge.