open file names from excel file

J

junjie

Member
hi bro
please help ...i'm so stressed now because i do not know how to resolve this : (
i have this working file, and now i want to open the files as mentioned in the columns E

View attachment 1534565718428.png

how to open this 2 files?
and in case i have more files (in this columns), how to let blueprism know and to continue open all if there is ?
 
V

VJR

Well-Known Member
- Use 'Get worksheet as collection' to get all the file names into a collection
- When you Loop through the collection you will get each file by using [CollectionName.ColumnName]
As soon you get the first file name, open the Excel file using the methods you have used in other Excel posts.
- Once your work is finished then close the Excel
- Then the loop will automatically do the same for the next Excel file.
 
J

junjie

Member
hi bro
after put the file into collection, then, this column got 2 rows of 2 files path
then, how to pick up the first file and open it ?
what action to loop through ? is it open workbook ? but we do not have workbook, we only have collection which just created ...
 
J

junjie

Member
hi bro
when we loop through the collection, it will pick up one by one, and store in a data item, and this data item will have 2 rows (2 file names)
but then, how to make blue prism open one by one ?
 
J

junjie

Member
hi bro
so, meaning that inside the loop there will be the calculation to get the file name and also an action open workbook , right?
 
J

junjie

Member
hi bro
one more issue.
after open this 2 files, i want to merge them into 1
they have same columns
so, i will turn them into collection first, right? but the thing is that we have just gone through the loop, and the data item is only 1
how we can turn into 2 collections then ??? (because we will need 2 data items right ? but the loop above generated only 1 data item)
 
V

VJR

Well-Known Member
Hi junjie,

- 'Open Workbook' for 'Final Excel File'. This is your Final file that will contain the merged data from all other files.
Create all the necessary handles using 'Create Instance'.
- Get the Worksheet into collection. This is the Excel file which contains list of all Excel file names (File1, File2,...etc.)
- Use a loop stage
- Open Workbook for Excel file 1.
- Use Copy Paste Worksheet Range from Excel File 1 to 'Final Excel File'
The loop will repeat for every file. During the copy paste you need to ensure to paste from the end of previous file. You need to do the same as you did the copy paste to find the last row (http://rpaforum.net/threads/how-to-delete-columns-in-a-collection.1344/post-5216)

- Save and Close Excel File
- Close the Loop


Also go through all the pages of the below link and take help from it.
http://rpaforum.net/threads/question-about-ms-excel-vbo-copy-and-paste-worksheet-range.580/
 
J

junjie

Member
hi bro !
we need to use loop before we get worksheet as collection, right? (before we get worksheet as collection for all the files listed in the column of the excel file, right ? )
 
Last edited:
J

junjie

Member
oh noooo... : (
I try to run and the copy paste of the 2nd File overwrite the first copy paste action : (
it's not continued from the last row of the first file copy paste : (
how to fix it ,,, : (
 
J

junjie

Member
hi,
could you please help advise the error is at which stage and what to put in source range and destination range ? :(
 
J

junjie

Member
hi bro : )
i try again , it works, but it pasted overwrite the first row also :( which is all the column headers now overwritten and become the 1st row of pasted data :(
 
You must log in or register to reply here.
Top