[Blueprism] Code stage to paste data as values in excel

Falua

New Member
Hi,

I have to copy some values from an excel sheet and paste them in another excel sheet of a different workbook.
The original action "Copy and Paste Worksheet Range" in the MS Excel VBO object does not allow me to paste values only.
I have duplicated the code stage for the action "Copy and Paste Worksheet Range" in the MS Excel VBO object, but can't seem to paste values only:


1526641064668.png

Thanks,
Falua.
 

VJR

Well-Known Member
Staff member
Hi Falua,

The code requires the numeric constants of parameters such as xlPasteValues.

You can find the numeric constant by doing a web search (the second one from the bottom)
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel

Or
if you write a one liner macro and run it...

Sub test()
MsgBox xlPasteValues
End Sub


...it will show you the corresponding constant
1526636339390.png

Also the above code doesn't appear to have a selection and Copy of the source data so that has to be added before pasting.
 

absak

Member
Helllo,

I had to do the same thing
using copy past it past the formula,

has anybody a code stage that copy values from a Range to Range, or how to do it throught Blue Prism
 

VJR

Well-Known Member
Staff member
Hi absak,

Below are 2 options with and without code-

Without code:
To copy only the data without formulas you need to have your stages as below-
- 'Select' action to select the source worksheet and the source range that needs to be copied
- Then the 'Copy' action to copy the selected cells onto the clipboard
- Then the 'Activate Worksheet' for the destination sheet if you want to paste this data into another sheet (or else use Activate Workbook for another Workbook)
- 'Go to cell' for setting the location where you want to paste this data starting from
- 'Paste' action with 'Values Only' flag as True.

With Code:
- Go to the code stage of 'Copy and Paste Worksheet Range'.
- Make a copy of it and add a small code change as below
- Comment the line in green by prefixing it with a single quote and add the below highlighted line.

1535007514209-png.1750
 

VJR

Well-Known Member
Staff member
Thank you VJR,

do you know how to Maximise an Excel Seet while it is running
Are you sure you would like to do it "while running"?
An excel running means it has a macro running on it. Is that the case with you?
Did you mean while it is open?
Also let me know are you spying Excel for some reason?
 
Yes it is the Case, I'm Spying Excel to Maximise it

With 'Set Window State' action in the MS Excel VBO object, the excel sheet can be maximised or minimised. To maximise, pass the value for the State Parameter as 'max' and to minimise, use 'min'.
 
  • Like
Reactions: VJR

absak

Member
Thank you AAH, i had another concern is that i'm running a series of Macros using run macro, the first run is good on the first sheet. but the second run fail
 

VJR

Well-Known Member
Staff member
Hi absak,

If you are already spying the Excel then you can do as suggested in the below post.
http://rpaforum.net/threads/need-to-maximize-excel-opened-as-an-instance.8103
Else you could also interact with the Ms Excel VBO as advised by AAH.
It would always be better to do with a VBO than spying.

Does the 2nd run fail when it is run without Blue Prism too?
Also by "fail" do post full details like what error you are receiving, what exactly is the macro doing and when does this happen (at which stage of the macro), etc.
 

absak

Member
Hello VJR,

Juste one question: when you write a collection a collection can you write to a specefic Range ??
 

VJR

Well-Known Member
Staff member
Hi absak,

Can you respond to the answers provided to your original questions before any new question.
The two answers in the earlier post are about maximizing excel and macro failing the second time.
If you have found a solution and no longer need it then you can respond by stating that too.


Juste one question: when you write a collection a collection can you write to a specefic Range ??
Regarding your new question, if you are referring about how to write a collection to a specific cell or range, then you can use the Cell Reference parameter. You can hover the mouse on it and see what is the meaning of the parameter.

View attachment 1539146704797.png
 
Top