Export Data from a closed workbook

Good evening/morning forum members,

I have received a lot of help from the members of this forum for what I'm really grateful. I have come across another issue that someone here might be able to assist me with. In the first attached file I have an empty template that based on the ID number (C1) would automatically populate data from a closed workbook with the same name as in C1 (2nd attached file). So basically here's the workflow:
1) An ID number is manually input in B1 and C1 creates a label to match the name of the corresponding file that is closed and stored in the directory C:\Users\Desktop\Test\Files
2)Macro is run and finds the file matching the name in C1 in that file path;
3)The file (ID_13) gets open and the data gets copied from it (A2:R2200) to Export Data file (E4:V2204);
4)The ID_13 file gets closed (I don't know if there is a way to not open the file at all-if it's possible it would be even better);
5) The final extract would look like in Table After Macro is Run tab.

Please let me know if I need to clarify anything.

Thank you!

Hi:

Find the attached.

You will have to add the following references in your VBA Editor:

• Microsoft ActiveX Data Objects 6.1 Library

Please Login or Register  to view this content.

Note: I am assuming that you will be storing all your files in the same folder as your macro file.

Thanks

Hi Ford,

I did initially use the vlookup but the master file is huge and it takes 20-30 minutes to get the data displayed once the ID number cell is populated.

Hi Nebu,

Thank you for the reply. Could you, please, elaborate on that Microsoft ActiveX Data Objects 6.1 Library comment? Also the macro file (the empty template) will not be stored in the same folder as the files it will be using. How will that impact the code?

Thank you!

Ford,
I think I've should have clarified - the master file contains all the IDs and I was using vlookup to pull the data from the Master file which is very large in size (108MB). Now I've split each ID's information into a separate files. So did you mean to use Vlookup on the separate files? If yes- how do I reference the right file (ID_#)?

Thanks.

Ford,

There are 927350 rows and 18 columns in it. How would the hardcoding file name/path work?

Thanks.

Ford,

What I did was I broken the master file out to create individual excel files for each id (4600 in total). Now I'd like to use the template where I'd input the id number in B1 and C1 would display the name of the individual excel file located on the local drive (please see the image). So, say, I want to extract data for ID 43. I input that id number in B1,C1 becomes ID_43 and the macro is searching for that file in the folder,finds it and extracts the data into from A2:R2200 to Export Data file E4:V2204. Please let me know if there is a simpler method of achieving this goal.

Thanks!

The master file contained transaction history for ~4600 loans. Since the file was huge I through macro extracted each ID's transaction history to a separate file using the "ID_#" naming convention for each file. So if, say, one ID history had 55 rows of transaction history the macro would extract it all into a separate file and name it "ID_#". I hope I answered your question.

Nebu,

So looks like I already had the Microsoft ActiveX Data Objects 6.1 Library reference added to my VBA editor. However, when I run the macro it gives me an error message (attached file). The macro is saved in the same folder as the individual files. Could you, please, assist?

Thanks.

Hi:

If the files are in different paths, you will have to replace the "ThisWorkbook.Path" with your file path.
and follow this link to learn how to add a library

http://excel-macro.tutorialhorizon.c...xcel-workbook/

Thanks

Nebu,

The files are located in the same folder and the mentioned library had already been added to my Excel but somehow i still receive the error message.

Can anyone, please, help with the error below? During debugging the error appears to be triggered by the last line below the highlighted one. Is anyone familiar with this type of errors?

Thanks!

Hi:

You are missing opening square brackets in the following line
rsSQL$ = "SELECT * FROM [Sheet1$]"

Thanks

I've noticed that as well but after the correction the error message still persists. Did it work for you?

Thanks.

Hi:

It is working perfectly fine at my end . I am attaching the files again save these files in the same folder and run the macro again.

Thanks

Thank you Nebu. The code in the uploaded file is working,however, when I deploy it in another file using the same logic (files are in the same folder and using the proper naming convention) the macro gives me an error. What am I doing wrong?

Thanks!

Also I've noticed that the tab I want to extract the data from is named Sheet9(Sheet1) in the editor even though I'm referencing to Sheet1 in the code.. i dont know if that's part of the problem.

Hi:

Yes, it should be the sheet name not sheet index that you should be referencing.

Thanks

What might be the root of the problem is that the referenced files were initially created in the same workbook and saved as separate files afterwards. So after each new worksheet was created Excel would count it in order. Therefore, even though the worksheet of the referenced file is named Sheet1 but in the VB editor it's shown as Sheet9(Sheet1). Basically, the macro is looking for Sheet1 in the referenced workbook but finds only Sheet9(Sheet1) and therefore return an error message. You think there could be any workaround?

Thanks!

The reason of the issue was because some of the individual files had a space after "_". Simple as that but took me so much time to figure out Thank you all for your valuable inputs!