Want to link a file with folder and sub-folders (at same location)

[smittal]

Good Morning All.

i don't know whether it is logically possible or not but, what i am trying to do ..

i am making a dashboard for senior management and currently we are handling more then 150 clients. There is a specific location where all the files and folders are saved. A folder (org name) and sub folders with different client name. there is a sheet called Master Dashboard which contains all the client name in Data Validation. Whenever we select any client name, then what i want is :-

"system should search the client name from folder list (inside main folder) and download the entire data of client sheet into master sheet (temporarily) and perform processing but in every folder we have around 4 or 5 worksheet (Based on service opt by client) and each worksheet contains n no of tabs and this downloading should be based on services.

Can any please confirm whether it is possible or not.

Thanks in advance for any help.

 

[lohithsriram]

Hi Smittal,
This is very much possible, if you have the folder/sub-floders named exactly as what you have on the data validation. Please refer to the below link, which is similar to your issue. You can copy and tweak the code in the link and post here if you get stuck somewhere .

http://chandoo.org/forums/topic/vba-copy-specific-data-from-all-workbooks-in-a-folder-problem

 

[smittal]

Thank lohithsriram.. actually i am new with Macros. Could you please tell me the logic of using "option explicit" because with using option explicit function some other macros stop working. it will be really helpful if you tell me little about this.

 

[lohithsriram]

Hi Smittal,

Option Explicit forces you to declare all the variables you have in your module/form/any other VBA object. Please refer below link for the details. As far as your issue is concerned, if you could get me a sample of what needs to be copied from the files under the subfolders to your main file, I can work an VBA code for you.

http://www.mrexcel.com/forum/excel-questions/4890-what-does-option-explicit-mean-do.html

 

[Smallman]

Hi Smittal

You would certainly be able to get more specific assistance if you provided a file with your post (sample data if you are dealing with sensitive information). The link in post 2 is generic code and you could get considerably more efficient coding if you were to provide a file.

At any rate this is a very commonly asked question and I am sure you can adapt something to your needs. Option Explicit is the most important error trapping within vb in my opinion. It is best practice to have it automatically appearing in all of your modules.

Anyways if we don't see your file, good luck with your project.

Take care

Smallman