+ Reply to Thread
Results 1 to 9 of 9

Open various files and fill sheet in a masterfile

  1. #1
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Open various files and fill sheet in a masterfile

    Hi experts,

    I have the following challenge, which I'm quite sure it's possible, but I've no idea how...

    In a certain directory on a pc are several partnumber files stored. I've attached 2 examples ("B661-6230" and "B661-6234"). These files will have a date in the filename as well, but the partnumber section remains unchanged. So the final filename should look something like "B661-6230 16072013" for a file from today. Tomorrow it reads something like "B661-6230 17072013" etc.
    There is a sheet in these files (can have variable names) in which various data is scanned / calculated.

    There is also a masterfile, for the whole familycode. In this example, familycode N94. This masterfile is also attached (file "N94"). In this masterfile you see various sheets for each partnumber, proceeded by the familycode. So you have e.g. "N94 B661-6230" and "N94 B661-6234".

    There is already a macro in the masterfile which pulls all the data from these individual sheets into the "Overall sheet" in the masterfile.

    What I'm missing now, is a macro which looks into a certain directory, opens all these several partnumber files and fills these individual sheets of the masterfile.

    The data itself is a simple copy / paste of the first 11 columns of each partnumber file.

    I hope that the below is a bit clear... in case you need further clarification / examples, please let me know.

    As always, many thanks for your time and effort to help me out here..!!!

    Kind regards,
    Peter
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Open various files and fill sheet in a masterfile

    http://www.rondebruin.nl/win/s3/win008.htm
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Open various files and fill sheet in a masterfile

    Thanks Patel. I'm familiar with the site you suggested. However, I'm struggling how to convert that code into a macro that would fill the various tabs in the masterfile.
    The link in your reply is pasting data from various files in a single worksheet. Is there some adjustment in the code that would do that?

    Thanks again for your feedback!!

    Kind regards
    Peter

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open various files and fill sheet in a masterfile

    Perhaps this will help:

    'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
    Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.The parts of the code that need to be edited are colored to draw your attention.
    Last edited by JBeaucaire; 12-27-2019 at 10:03 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Open various files and fill sheet in a masterfile

    Hi Jerry,

    Thanks for the above. Apologies for the late reply, holiday came in between ;-)

    When I have 2 different files in 1 directory and I run the macro in the mastersheet, it is copying all data from the 2 files into the mastersheet. So far, no problem.
    The problem starts when I add a 3rd file in the directory and run the macro again.
    The macro is then simply copying all the data from the first 2 files again in the mastersheet. So I have duplicate data.

    Is it somehow possible to adjust the existing macro so that it disregards the known lines already in the mastersheet and simply adds the additional lines?
    We can put a date in the filename (as per examples), perhaps that can be some kind of criteria to use or disregard the file?

    Attached is a masterfile ("M68.xlsm") and 3 datafiles from which the data is copied.

    In case of any questions, please let me know...
    As always, many thanks in advance!!!

    Kind regards,
    Peter
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open various files and fill sheet in a masterfile

    Simplest solutions:

    1) Clear the data in the master sheet before importing again, all data is imported anew
    2) Move the previously imported workbooks from that source folder so they are not reimported on future imports.

    This amendment does #2:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 08-22-2013 at 01:35 PM. Reason: forum format correction

  7. #7
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Open various files and fill sheet in a masterfile

    Thanks Jerry, that should indeed do the trick.

    However, I get a syntax error when running your macro, on the section below:

    Please Login or Register  to view this content.
    I should probably fill in some name or path, but I can't figure this out...

    Can you explain what this section does and what I need to do here?

    Thanks again!!

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open various files and fill sheet in a masterfile

    The forum merged two lines of code, END IF goes on the next line. I corrected it above.


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Open various files and fill sheet in a masterfile

    Aha, perfect, thanks Jerry!!!

    Case closed, star earned

+ 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. Macro open files and copy sheet and rename sheet
    By matrix_xrs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2013, 04:33 AM
  2. Macro to get data from individual files and add it into one common masterfile
    By lfmemp in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-04-2012, 05:06 AM
  3. [SOLVED] Open column of url's as csv files and append as new sheet
    By rgilpatric in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-04-2012, 09:18 AM
  4. [SOLVED] Open all files in a SharePoint folder (Problem: Files open out of order)
    By holmstarrunner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2012, 10:13 AM
  5. A Macro to open 2 linked files copy 3rd files Input sheet
    By barrha0b in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2010, 09:53 PM

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