I come here to ask for some help from my VBA expert's friends.
I have about 150 .xls files where I have the measurements of the buildings where I've worked in and I need the data of these 150 workbooks in a single main file where I have monthly measurements with a general report and charts.
This main workbook has fields with pathnames which I created using concatenate, generating an auxiliary spreadsheet that has external reference to each secondary's file.
It has about 70 sheets in the file, from which 50 sheets are summary of monthly measurements; And what I want is automate the data collection through the closed workbooks without using indirect function neither open all woorkbooks to update the main file.
I researched a lot about "dynamic external references" and "pathnames for Dynamic External Links" and similar issues ... but I couldnt go ahead cause my VBA's knowledge is s*cks yet.
So... the idea is create a macro which uses formulas and autofill function in VBA to copy concatenated values, similar to excel's native external reference, to open and update data from all my 150 closed workbooks to this 50 sheets in the main workbook, without need to use the INDIRECT function and without need to create macros that open all 150 workbooks. Here is a link to "Rylo" and "Freud1"'s insight
Dynamic Pathnames for External Links
I ll try post my cleaned main woorkbook.
Sorry about my poor english language skills.
and Thanks all.
I have about 150 .xls files where I have the measurements of the buildings where I've worked in and I need the data of these 150 workbooks in a single main file where I have monthly measurements with a general report and charts.
This main workbook has fields with pathnames which I created using concatenate, generating an auxiliary spreadsheet that has external reference to each secondary's file.
It has about 70 sheets in the file, from which 50 sheets are summary of monthly measurements; And what I want is automate the data collection through the closed workbooks without using indirect function neither open all woorkbooks to update the main file.
I researched a lot about "dynamic external references" and "pathnames for Dynamic External Links" and similar issues ... but I couldnt go ahead cause my VBA's knowledge is s*cks yet.
So... the idea is create a macro which uses formulas and autofill function in VBA to copy concatenated values, similar to excel's native external reference, to open and update data from all my 150 closed workbooks to this 50 sheets in the main workbook, without need to use the INDIRECT function and without need to create macros that open all 150 workbooks. Here is a link to "Rylo" and "Freud1"'s insight
Dynamic Pathnames for External Links
I ll try post my cleaned main woorkbook.
Sorry about my poor english language skills.
and Thanks all.
