Best way to link data from several (10) workbooks files apart

I'm in need to link data from different workbook excel files consolidating into 1 unique file. Usually I simple open the file and link the data I want to the file one by one. I was wandering if I can do this in a more automated way. Some times when I use the same template and the data position are in the same "location" in each file I just copy and paste the link and then change de file name, without needing to open the file. It works most of the time.

After the initial process of linking the data another thing I want is a more efficient way to manage the links integrity. The excel built in data connections is very poor and some times dont works. It does not allow me to break links or open sources. Is there any addin with improved data management capabilities? It can be a paid one, does not need to be free.

I do what have to do, my objecive here is to increase my productivity and not to loose so much time doing a mechanical work. I want to speed up my workflow. Any tip is wellcome.
Thanks in advance

If you have a lot of files in a folder that all have data of interest in the same location on the same sheet, then your can use a macro like this (read the comments to customize the values used for the linking)

Please Login or Register  to view this content.

Nice start point, what I'm thinking in doing is to have the code feeding from a file where I can set the filenames that I want to copy from. Thanks

@souza

Please do not quote entire threads when responding. It is only clutter. Instead, just click on the reply and not reply with quote. The person you are replying to will know what they have posted for you.

Alan