Extract data from multiple external excel files

Hi ALL,

I currently have an excel spreedsheet that track all drawings issued using formulas but as the number of drawings and stages of transmittals increases the file also increases. Drawing list and Transmittal list will be a DB_dump from our drafting software.

I am just a novice to Excel Programming and I need help develop a routine to extract data from multiple external source.

Workflow:
1. evalauate DWG# from Trans# against Master Drawing Control register and extract Rev No. (see attached screenshots). Also attached the sample excel files.

Trans3.xlsx
Master Drawing Control Register.xlsx
Trans2.xlsx
Trans1.xlsx
DWGLIST.jpg

Maybe :

Please Login or Register  to view this content.

@karedog
I tested your code within the "Master Drawing Control Register.xlsm" and it works as intended. But when I moved the code to my Personal.xls file (located at excel start-up folder) which stores all my VBA/macro collection, it's not working, Is it possible to do this or it must be within the same worksheet.

@MarvinP
Thanks for the link.

Try to change the word "ThisWorkbook" into "ActiveWorkbook" (2 occurences).

It is not a good idea to move macro which worked for specific project to Personal.xls. Do it only for functions or subs that should work for general purpose.

I manage make it dynamic.

Please Login or Register  to view this content.

You are welcome, and thanks for the reputation point.

You don't have to clear existing data first, because the array output size is set as the same size of output range, so all existing data will be totally overwrited by the output array.

But if you want to know how to clear dynamic range manually, here is the way :

Please Login or Register  to view this content.