Pull specific data's from variable and closed workbooks over network

[rayn3]

Hi,

I'm struggle for some days to do mainly what i have said above in the title. In one workbook, i want to pull some data from multiple and variable workbooks over the network. This last workbooks can change over the time, but all the time, they will have the same format of the worksheet. They are one template but with different data and different names. Filenames i have pull them in my report file and now base on this i want to pull also some data's inside of them. Of course this files are closed. I've tried with INDIRECT function, but it works only if the files are open, so is not working for me. I'm not so familiar with vba, only that i know here is laying my resolving. I have attached one example of my report file. Could someone give me some hints?!?! Perhaps there is something could work with formulas...Otherwise if someone is kind and help me with the vba code...?!?! I'll kindly appreciate. Thanks in advance.

[oeldere]

If all data is in the same directory you can use this VBA code.

It will copy the range you selected to a new workbook.

You have to amend the red text to your situation.

Please Login or Register  to view this content.

[rayn3]

Hi. I'll check it and come back with input. One remark. I have two folders over the network where it should look, depending by the name of the filename in my report. But i'll check it as it is and reply to you later. Thank you.

[oeldere]

There will be a better way, since I am not a VBA expert, but in that case I would run the code twice (with a differant directory name).

[rayn3]

I've checked your code, is doing mainly what you have said above, only that:
1. i already have myreportfile.xls(close to the one i've attached at the beginning of this thread). So, i don't want to generate a new workbook, i want to drop all the values in this Main workbook report.
2. i want only some specific data's from all the files-templates over the network. For instance, i want to check some cells where is mentioned Start Date and Due Date. For all the files over the network i want to check for this dates and drop them in my report in two separate columns.

[oeldere]

Did you tried to run the code?


If you only want that range (D10:E10) you can change that in:

Set sourceRange = .Range("D10:E10")

[rayn3]

Yes, i run it. I noticed now that i've missed this. Anyway, i'm not so familiar at all with vba, this could be the reason. Thanks.

[oeldere]

After you change the range (and directory), the code is working for you?