Access data from a closed workbook

[madvogue29]

Hi all!

I have a report that is generated weekly for example 2401, 2402, 2403 etc. Is there a way that i can access data of these workbook from a master sheet. I have a formula that basically concatenates to the sheet name eg P:\weekly exports\2404.xlsm
Is there a formula or vba that lets me access data from this wb using the value from the concatenated name like cell F20 from the sheet and input it in Cell F25 in the masterworkbook sheet 1 ?

Thanks,
Mo

 

[Micron]

Your question doesn't quite reflect the title, so I'll go with the title of the thread.
No, you cannot get data from a closed wb. However, you can open a wb hidden and copy the data using code in the wb that you already have open (Master?). There are tons of hits if you Google how to open a wb hidden, and how to copy from other workbooks. Or maybe you'll find code that does both rather than having to piece the parts together. The code would use your cell reference to know where the file is, which is what I think you asked.

 

[madvogue29]

Your question doesn't quite reflect the title, so I'll go with the title of the thread.
No, you cannot get data from a closed wb. However, you can open a wb hidden and copy the data using code in the wb that you already have open (Master?). There are tons of hits if you Google how to open a wb hidden, and how to copy from other workbooks. Or maybe you'll find code that does both rather than having to piece the parts together. The code would use your cell reference to know where the file is, which is what I think you asked.

Thanks for updating the title. I can access data from unopened workbook like this ='P:\Test\[Book1.xlsx]Sheet1 (2)'!$A$1 however if i use concatenate to form this combination it doesnt work.

 

[madvogue29]

Would Indirect work somehow ? i tested but i get #ref with the same reference

 

[Micron]

If the title was updated, that's not me - not something I can do. I neglected to mention that I was answering the "VBA" portion of the options you mentioned. Formulas are not my thing. At my age, I have a better chance of winning the lottery than me becoming a formula guru.

 

[madvogue29]

haha thanks anyway! i used this and modded it to use it. it work but its too slow for my usage. 1 min runtime per execution. VBA Express : Excel - Extract Data From Closed Workbooks

 

[Micron]

Try disabling the Do Events line and see if that makes any difference.