+ Reply to Thread
Results 1 to 25 of 25

Export Data from a closed workbook

  1. #1
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    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!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Export Data from a closed workbook

    It sounds to me like you could do this a lot easier just using vlookup()? (it works on closed workbooks)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-14-2015
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    12

    Arrow Re: Export Data from a closed workbook

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.

  5. #5
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Export Data from a closed workbook

    Quote Originally Posted by mamuka12 View Post
    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.
    what did the formula look like that you tried?
    I have tried this method before when teh data file was too large to work with

  7. #7
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Export Data from a closed workbook

    That IS a big file, how many rows./columns in it?

    If your ID's are logically broken out into different files, you could hard code the file name/path?
    (INDIRECT wont work because that only works on open files - unless you install the MoreFunc add-in)

  9. #9
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    Ford,

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

    Thanks.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Export Data from a closed workbook

    What I meant was - you said you had broken the names out into different files. If there is some logic to that...A-J in 1 file, K-T in 1 file, etc, you could use that as the trigger for what file to use

  11. #11
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!
    Attached Images Attached Images

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Export Data from a closed workbook

    You still have not answered my question lol...is there any logic to how you split the names, or did you just take the an arbitrary number rows and make a file from them - and so on?

  13. #13
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.

  14. #14
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.
    Attached Images Attached Images

  15. #15
    Registered User
    Join Date
    10-14-2015
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    12

    Re: Export Data from a closed workbook

    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

  16. #16
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.

  17. #17
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!
    Attached Images Attached Images

  18. #18
    Registered User
    Join Date
    10-14-2015
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    12

    Re: Export Data from a closed workbook

    Hi:

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

    Thanks

  19. #19
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

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

    Thanks.

  20. #20
    Registered User
    Join Date
    10-14-2015
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    12

    Re: Export Data from a closed workbook

    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
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!
    Attached Images Attached Images

  22. #22
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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.
    Attached Images Attached Images

  23. #23
    Registered User
    Join Date
    10-14-2015
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    12

    Re: Export Data from a closed workbook

    Hi:

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

    Thanks

  24. #24
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!

  25. #25
    Registered User
    Join Date
    09-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Export Data from a closed workbook

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export filtered data into different closed workbook as new worksheet
    By Griffin29 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-16-2014, 06:24 AM
  2. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  3. Export Excel data into closed workbook
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-09-2013, 12:15 AM
  4. Export data from closed workbook to active workbook with ADO connection
    By Naresh27 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-09-2013, 01:08 PM
  5. Replies: 12
    Last Post: 07-03-2013, 01:35 PM
  6. How to export data to a closed Workbook
    By newbie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2005, 05:05 AM
  7. Export data to a closed workbook
    By Luis in forum Excel General
    Replies: 0
    Last Post: 05-21-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1