Select File(s) from a Directory for Processing

[TheEmeraldKnight]

H there,

I want be able to select a file or a number files from a given directory and for each selection copy the data to a specific tab in a consolidated Excel macro-enabled workbook.  To help with this the the source files start with B[NNN] so B001 - Daily TB would go the tab B001 in the consolidated workbook.

I have gotten as I can with the attached screen shot (the data is sensitive so can't post the workflow) but it shows all files in the directory where I only want ones that start with B[NNN] and keeps asking for the directory.

Any help to get this workflow working would be appreciated.

Regards,

Robert

[caltang · Accepted solution]

@TheEmeraldKnight I think your issue is that you are now stuck at the load phase. You've got the selection down but you don't know how to call the data out and manipulate it.

Here's how:

In your second workflow, you need to add a LIST OF SHEET NAMES MACRO which I created for you. It will dynamically go search for each sheet name in your files from your directory after you selected them. Then, it will load the data from your selected files as one big stack.

I've attached it and you can study it. You can also mark this as a solution if it helps.

[TheEmeraldKnight]

Hi Calvin,

Could you please also share Workflow2?  I started going the path of using a list box to select which container(s) to run which though slow seems to be working for the first three imports:

One other consideration is that there be multiple files with the same prefix to import into the consolidated workbook i.e. B005 will have a separate report for Australian and Singapore.  I have used the * wildcard within the containers to achieve this.

Many thanks,

Robert

[caltang]

It's inside the YXZP I gave you:

[TheEmeraldKnight]

Sorry Kelvin - rookie mistakes.

I know you say "you need to add a LIST OF SHEET NAMES MACRO" but I don't know to do that I'm afraid.  I see a XML panel open when I click on the tool (that has now become a question mark) but not sure how to populate the tags.

The following is a list of the files B001 - B015 of which each have a separate tab in the Suspense Template.xlsm workbook i.e. B001, B002, B003...B015.  The idea is just to be able to select one or more of the B[NNN] tabs that will then be copied into their respective tab within the Suspense Template.xlsm workbook.  There are times that the same file layout may need to be copied in (consolidated) multiple times like B005.

Regards,

Robert