Get files from collection

waziaks

Member
Hi Champs,

I am in a process to consolidate data from various workbooks located in a folder. I am able to get those files into collection using Get Files from Utility File Management.
How can I open those files one by one from collection?

Please help
 

Attachments

  • BP.JPG
    81.7 KB · Views: 31

jacobs35

New Member
its not a file management function inside the loop. you will need to use the Excel VBO.
Once there you can choose your own adventure on how you want to consolidate the data. Generally, i would move the data out of the excel files and into collections. then consolidate the collections. If you are doing lookups between them you may also require an additional loop.
there are some good youtube videos you can view for some basics on working with excel files. i'd recommend you go find them.
 

waziaks

Member
Jacob - What to do If i want to follow your approach?
I've around 10 workbooks in one folder how can I move all data the into one collection? Please specify the steps and I would really appreciate if you proivde some screenshot as well.

Many thanks
 
Jacob - What to do If i want to follow your approach?
I've around 10 workbooks in one folder how can I move all data the into one collection? Please specify the steps and I would really appreciate if you proivde some screenshot as well.

Many thanks
Use the Excel VBO. Create an Excel Instance.

Loop through your collection of Excel files, each time use the Excel VBO action Open Workbook using the path given in the collection. Use one of the Excel VBO Get Worksheet as Collection actions (look through the different options and choose the one most suitable). Once you have extracted the worksheet into a collection, append that to a single master collection that will contain all of your data (look through the Utility - Collection Manipulation actions). Continue through the loop.
 

waziaks

Member
Thanks for the the help. When I tried to append rows from my main collection to new collection each time i am receiving the error. I've attached the error. Maybe, its because I am using append command from Utility - Collection Manipulation and my new collection is empty. Any thoughts on this?
Also please let me know the solution for this or which command should I use from Utility - Collection Manipulation?

Thanks
 

Attachments

  • collection error.JPG
    17.8 KB · Views: 22

waziaks

Member
I've managed all of the suggested tasks above by (nickbell123) except this one (Once you have extracted the worksheet into a collection, append that to a single master collection that will contain all of your data (look through the Utility - Collection Manipulation actions). Continue through the loop.). When I tried to append the data it's completely remove the all the data from the collection

Kindly help. All i want to do is to copy all the data one by one into master collection
 
I've managed all of the suggested tasks above by (nickbell123) except this one (Once you have extracted the worksheet into a collection, append that to a single master collection that will contain all of your data (look through the Utility - Collection Manipulation actions). Continue through the loop.). When I tried to append the data it's completely remove the all the data from the collection

Kindly help. All i want to do is to copy all the data one by one into master collection
Use Append Rows to Collection action - your master collection (which will be empty on the first time around the loop) should be the Main Collection input, and your new collection should be the Collection to Append input.

Make sure you set the output of the action to be the master collection.
 

waziaks

Member
Hi

I've one created one tab inside the MS Excel VBO to filter out some data in excel sheet. I've provided the parameters such as handle, workbook, workbookname (refer Filter_1 Excel vbo screenshot2). But when I am calling that Filter VBO through Action parameters not showing up(refer Filter_2 Excel vbo screenshot3). I've attached the screenshots. please let me know what I am doing wrong..thanks
 

Attachments

  • Filter_Excel vbo.JPG
    72.4 KB · Views: 19
  • Filter_1 Excel vbo.JPG
    61 KB · Views: 15
  • Filter_2 Excel vbo.JPG
    60.6 KB · Views: 16

VJR

Well-Known Member
Hi

I've one created one tab inside the MS Excel VBO to filter out some data in excel sheet. I've provided the parameters such as handle, workbook, workbookname (refer Filter_1 Excel vbo screenshot2). But when I am calling that Filter VBO through Action parameters not showing up(refer Filter_2 Excel vbo screenshot3). I've attached the screenshots. please let me know what I am doing wrong..thanks
If you need them as Input parameters they also need to be set in the Input of the Start stage of the Filter action.
If you need any Output parameters from the Filter action then pass them from the End stage of your new action.
 

waziaks

Member
Hi There,

Is there any function or command which I can use inside the code to select all the data from excel worksheet at one go. I've used currentregion (VBA technique) but didn't work for me.

Thanks in advanced
 
Thanks it's working now.

Is there any way to get unique list from any column or remove duplicates?
How do you determine what a duplicate is? On one field or multiple?

You could loop through the collection, filter the collection based on your criteria, if you get results it is not unique, if you get no results it is unique.

You could copy all unique rows to a new collection.

Or build a code stage similar to this
 
Top