Open excel files from chosen folder

Hi,

I have a script that automatically imports all the excel files from a folder and puts them into 1 "master" sheet.
The 'problem' with this is that the folder is preset in the script.

Is there a code i can use, that does the same but when you start the macro excel opens a exploror window where you can click a folder where it needs to extract the files from?

This would make my macro somewhat more dynamic.

Thanks for all the help.

Hi Parth,

Thank you for your answer. The method i was talking about is in another template and i'm creating a new template for other purposes so i don't have a file yet. But if you could give me the code i can put this in my new file.
Thank you so much.

Thanks man, this is awesome! I will check it out!

Hi Parth,

I tried the code + function and changed csv to xls, but it doesn't import the files.
Since i'm pretty noob at vba, could you take a look?

I added the file.

Thanks

Maybe i was unclear in my question, sorry if this is the case.
It should move and copy the tabs from the sheets in the folder into the 'master' sheet.

Hi Parth,

Cool, thanks!
I'm checking it now. However it gives me an error

runtime error 9
subscript out of range

on line:

[CODE][/ ActiveWorkbook.Sheets(x).Copy _
After:=Workbooks("import files").Sheets(3)CODE]

this is in the macro Copier4()

Hi Crispy,

Try the attached Workbook that should work for you
@Parth your solution was not working correctly too cause you used activesheet as reference to the sheet that you want to copy from.
Problem with that is that as soon as you paste the Sheet to the Sourcefile the source becomes the active sheet and then starts to copy itself.
This way its copying correctly and the code is a bit more dynamic

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Cool. Both thanks for your efforts!! It works!

I still have one question however.
The folder only contains .xls files. So i changed it in the script from xlsx to xls. But now i see that it doesn't import everything. And i see why, some file have extension xls and other have XLS.

Do you know how to fix this in the script?

Cool, thanks guru's!