Transpose in blue prism

shashacool

New Member
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
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:

shashacool

New Member
thanks bro.... i want copy range vba code so, that i can copy the range column wise and paste that range to row wise.......
 

Kaushik

New Member
[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