VBA Automatically Open Files In Folder

Hi, I wonder whether someone may be able to help me please.

I'm using the script below to allow a user to open multiple files, extracting a range of information before pasting this into a "Summary" sheet.

Please Login or Register  to view this content.

In it's current for, the user has to select each file to open, but I'd like to adapt this so the user only has to select the folder and the files open automatically and extract the data, but I'm very unsure about how to do this.

I just wondered whether someone could possibly look at this and offer some guidance on how I may go about amending this.

Many thanks and kind regards

Hi Hobbiton73

Try this code

Please Login or Register  to view this content.

See you later

Hi @BrianM45, thank you for taking the time to reply to my post and for putting the solution together.

I've tried the code you kindly provide, but unfortunately I receive a Run time '1004' error with the message "Sorry, we couldn't find File1.xls. Is it possible it was moved, renamed or deleted". Debug then points to this line:

Please Login or Register  to view this content.

This is a little puzzling though because File1 does exist and is in the folder I choose.

Kind regards

Hi @mrice, thank you for taking the time to reply to my post.

Forgive me for askng, but could you possibly offer a little guidance how I may incorporate the full file path please.

Many thanks and kind regards

Hi Hobbiton, Hi Mrice

sPath return the path who choose whit back slash
sFile is the file who is in the directory

The correct code is

Please Login or Register  to view this content.

Sorry for my late reply

Hi @BrianM45,@mrice,thank you both for taking the time to come back to me with this.

@BrianM45, your amended ccode works great, thank you.

Both, all the best and kind regards