How to delete columns in a collection

VJR

Well-Known Member
Hi,

The above Calc stage is written vice versa

This is what I have mentioned..
You will do this by writing "Yes" in the Calc stage and in the Store In section of Calc stage [CollectionName.Checking].

But in the Store In section you have mentioned a Yes.
 

VJR

Well-Known Member
In this screen don't click the small button because your collection is already created with that column name.
Leave it with Data 1.Checking and run the process.

1529198820243-png.1078
 

junjie

Member
Hi VJ
noted with thanks :)
now, i'm struggling with this error that i cannot open the workbook...

1529392846244.png


it pops up at the stage Action: Get worksheet as collection.

can you help me where is the error ? :(
 

VJR

Well-Known Member
Hi junjie,

i. Can you go to that path and check whether the below are correct
-file path
-file name
-file extension (.xls, xlsx, xlsm?)

ii. Check all the parameters are correctly passed like Handle, Workbook Name, etc
Note that the Workbook Name data item automatically generated (don't generate manually) from 'Open Workbook' action should contain only the "FileName.xlsx" and not the complete path.

iii. If they are correct then share the properties of 'Get worksheet as collection' along with the parameters passed
 

junjie

Member
but i notice, there's an small mismatching
the first row, the data cannot appear correctly, it should be 1 (as shown in the excel file), but in the collection it appears as #N/A (you refer to above screenshot of the collection data)
it is because there's a vlookup formula there ?

1529422475757.png
 

junjie

Member
by the way, i extend my sincere appreciation to your kind help :)
i really can't believe that i can make such progress on my process, that i was almost giving up from the beginning, even though now i still have more steps ahead to do ...
again, thanks a lot !
you're a good expert !
:)
 

VJR

Well-Known Member
hahah aiyo, there's a space in between Data and 1
okay resolved :)
To avoid such issues in naming you just need to drag and drop the name of the data item from within that same window itself instead of manually typing the name. Blue Prism provides a feature of showing a list of all data items in the window which can be simply dragged.
 

VJR

Well-Known Member
hi VJ
by the way, how to insert a column, let's say insert between column F and G ?
From where are you going to insert a column?
If Colulmn G does not have any data yet, then you can simply use 'Set Cell Value' action.

Set Cell Value on column H and make it 'Checking'
Set cell Value on column G and make it 'You new column name here'
 

junjie

Member
hi VJ
i have another task but still stuck : (
i have an excel file 1 of 20 rows
another file 2 of 10 rows
both files have the same data (same columns)
i want to copy paste the data of file 2 into file 1 (append continue from the last row of file 1)
i am doing as below:
open workbook of file 2 >> get last row in range ( inside property, at the Home Cell Reference, i put "A2:AA" is it correct? what i mean we copy from row A2 and copy other columns data as well, but we extend up to column AA to cover as much as possible, is it correct? ) >> then Action copy and paste , inside property of this action at the Source Range put "A2:AA" right ? and Destination Range will be "A2:AA" & [find last row]
Is it correct ?
 

VJR

Well-Known Member
hi VJ
which part has error ?
also, i look at your guides from other topics they ask about copy paste, but my paste is to paste continue from the last row of the file 1 , how to do ?

View attachment 1252

The steps would be as follows:

1. Find Last Row of File2 (using 'Get Number Of Rows' of MS Excel VBO)
eg; [LastRowFile2]

2. Find Last Row of File1
eg; [LastRowFile1]

3. In Copy Paste Worksheet Range-
give 'Source Range' as "A2:AA" & [LastRowFile2]

4.
give 'Destination Range' as "A" & [LastRowFile1]
 

junjie

Member
VJ ~
please help me this :(
during the comparison and to put Yes if matching and No if not matching.
for example, the column A value 25.00, and the other column B value is 25
in fact, they are the same
however, RPA recognize the "25.00" as a text, and the "25" also a text, and this case makes the comparison becomes No
could you help me advise how to make it a Yes (because they are actually the same value)
appreciate so much your help !!!
 

junjie

Member
let me explain a bit further.
in the excel file, the column A value when we place the mouse at the cell, even it shows 25.00, but the excel recognizes it still 25
however, in RPA and the comparison is doing after we put data in a collection, and in this collection, i found that the column is a text, and the ".00 " transmit exactly to collection column and the value shows 25.00, and that's why it gives the answer No upon comparing :(
 
Top