Blue Prism - MS Excel - Copy Paste Column all formats

LukasSimkus — Thu, 19 Mar 2020 11:58:00 GMT

Hello,

I've tried to make a VBA code to Copy and Paste column data in the same worksheet i.e from Column A to Column B with exactly the same format (Font, Alignment,Formulas etc.). Unfortunately, it only pastes data with formulas without alignment, font etc.

Does anyone know how to adjust that?

This is the code: Bold text should be adjusted for sure.
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
Dim destination As Object
Dim xlUp as Integer = -4162
Dim RangeEnd as String
Dim xlPasteValues as Integer = -4163
Dim xlPasteValuesAndNumberFormats as Integer = 12
Dim xlPasteFormulasAndNumberFormats as Integer = 11
Dim xlPasteAllUsingSourceTheme as Integer = 10
Dim xlPasteColumnWidths as Integer = 9

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

'Get coordinate of last row in specified column
RangeEnd = sheet.Cells(sheet.Rows.Count, Range_End_Column).End(xlUp).Address
'Copy range
sheet.Range(Range_Start_Cell, RangeEnd).Copy

'Activate destination workbook and worksheet
dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet

destination = sheet.Range(Destination_Range_Start_Cell).Activate

'Paste data as formulas
sheet.PasteSpecial(xlPasteFormulasAndNumberFormats)
sheet.PasteSpecial(xlPasteAllUsingSourceTheme)
sheet.PasteSpecial(xlPasteColumnWidths)

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
End Try

------------------------------
Lukas Simkus
Developer
Outokumpu Stainless
Vilnius
------------------------------

RE: Blue Prism - MS Excel - Copy Paste Column all formats

Carl__Carter — Sat, 28 Mar 2020 07:01:00 GMT

Hi Lukas,

The correct Constant value for xlPasteColumnWidths is 8 and not 9.

Also, rather than using the xlPasteAllUsingSourceTheme Constant, use the Paste-Special operation - Paste Formats. The Constant for this operation is xlPasteFormats which has a Constant value of -4122

Regards,
.

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------

topic Blue Prism - MS Excel - Copy Paste Column all formats in University Forum

Blue Prism - MS Excel - Copy Paste Column all formats

RE: Blue Prism - MS Excel - Copy Paste Column all formats