Transpose in blue prism

#1
i am coping the some values from one excel (as column wise) in collection and pasting it to another file (as row wise ). here, i used transpose collection from collection manipulation but not getting desired output.
pls help!!
 

VJR

Well-Known Member
Staff member
#2
Hi shashacool,

Transpose collection won't work in this case.
You need to actually paste the data as Transpose option (the one used in Excel).
In order to do that,
- Make a duplicate copy of the 'Paste' action of the Ms Excel VBO.
- Give the Action and the Code stage a new name like 'Paste as Transpose'
- Go to the Code stage and simply make the below change (the one shown in bold).

From this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=False)

To this:
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=True)


- Save the Object, Reset, Refresh and call this new action from the Process.
- This should resolve your issue.
 
Last edited:
#5
[Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

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
cell = excel.ActiveCell


cells = sheet.Range(Source_Range)

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
'destination.Select()
destination.PasteSpecial (Paste:=-4163, Operation:=-4142, SkipBlanks:=False, Transpose:=False)
'MSgBox("1") -4163
'sheet.PasteSpecial (Paste:=-4122, Operation:=-4142, SkipBlanks:=False, Transpose:=False)
'sheet.Paste()
cell.Select()

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
source = Nothing
destination = Nothing
cells = Nothing
cell = Nothing
End Try]
 
Top