Search code examples
excelvbablueprism

VB Code to Paste as values and Source Formatting

I am using Blueprism to automate a process which uses Excel. I need to copy certain cells and paste as values and source formatting. I am using the following code. After running the job the values were still formulas and not values.

Dim wb, excel, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
excel = wb.Application

range = excel.Selection
range.PasteSpecial(Paste:=-4163, SkipBlanks:=False, Transpose:=False)
range.PasteSpecial(Paste:=-4122, SkipBlanks:=False, Transpose:=False)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
range = Nothing
End Try
Solution

  • If you want to paste values and number formats it should be Paste:=12.

    For paste values it's Paste:= -4163.

    Paste:=13 is for 'paste all using source theme'.

    To paste values and all formatting:

    range.PasteSpecial(Paste:=-4163)
range.PasteSpecial(Paste:=-4122)