VBA Code to Pull out the data from the closed workbook

Hello Everyone,

I need to fetch out the data basis on some criteria's from the closed workbook. As I know it's possible only through VBA code. Explaining with an example;

Pull out the data in "Sheet1", Column D basis on criteria's in Column A, B & C from "Sheet2" (multiple tabs). Hope I'm able to explain. Can anyone in the forum please help me out. I am scratching my head from the morning but not been able to resolve it. Attaching the sheets for your reference.

Many Thanks in Advance.

Best Regards,
Abhishek

Hello, this does not require VBA.

Menu Bar > Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* > Paste path to excel file into "Database name" field, hit Enter > Select the Excel file you wish to query from.

If you have the both sheets on same folder then write the following array formula on cell D2 of Sheet1 and copy it down. That's it.

Please Login or Register  to view this content.

To confirm an array formula press Ctrl+Shift+Enter.

If you have that Sheet2 on different folder then change each "[Sheet2.xlsx]"&$A2&"! to "'C:\Users\user\Desktop\[Sheet2.xlsx]"&$A2&"'! on above formula, where replace C:\Users\user\Desktop\ with the address of Sheet2.

Thanks Sanram for the response. The formula is working fine when the Sheet2 is open. But actually in my case Sheet2 is closed. Is there any way that I can do the same thing when the Sheet2 is closed.

Best Regards,
Abhishek

Thanks Poizhan for the response. I tired using the method you mentioned but getting an error as "This data source contains no visible tables". No idea if I am doing something wrong. Could you please elaborate by using the same in sample attached files. Thanks.

Best Regards,
Abhishek

Originally Posted by abhi_jain80

Thanks Sanram for the response. The formula is working fine when the Sheet2 is open. But actually in my case Sheet2 is closed. Is there any way that I can do the same thing when the Sheet2 is closed.

Best Regards,
Abhishek

This formula will work all the time. To get the updated data from the formula press F9. That's it.

It the Sheet2 is closed, the formula is not working. The error coming as #REF!...If I open up Sheet2, the values appears. No idea if I am doing anything wrong or might Indirect function won't work on closed workbooks.

Best Regards,
Abhishek

Originally Posted by abhi_jain80

It the Sheet2 is closed, the formula is not working. The error coming as #REF!...If I open up Sheet2, the values appears. No idea if I am doing anything wrong or might Indirect function won't work on closed workbooks.

Best Regards,
Abhishek

#REF! means the address of sheet2 file is wrong. Please check that.

Originally Posted by sanram

#REF! means the address of sheet2 file is wrong. Please check that.

@sanram,

INDIRECT won't work with closed file.

Originally Posted by Indi_Ra

@sanram,

INDIRECT won't work with closed file.

Is there any other option to resolve the issue.

Originally Posted by Indi_Ra

@sanram,

INDIRECT won't work with closed file.

Thanks for the info. I didn't know that before.

Try

Please Login or Register  to view this content.

Alter "1000" to site.

The solutions you provided is absolutely working fine. Thanks everybody. This could be a great inventory for the others in future.

Best Regards,
Abhi