Search code examples
excelvbablueprism

VB To Copy Excel Sparklines

I am using a Blueprism Process to run a VB Script. It copies a range of excel values and pastes as source formatting. When it runs it does not paste sparklines correctly.

Code:

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

  • Not sure why you make 2 paste special. But Firstly you paste only values, later only format:

    xlPasteValues -4163 Values are pasted. xlPasteFormats -4122 Copied source format is pasted.

    And you could do this in one line:

    xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

    But if you want to paste as source use this one:

    xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.

    Source: https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype