+ Reply to Thread
Results 1 to 18 of 18

Extract data – Multiple Files – to Single File – in Excel

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Extract data – Multiple Files – to Single File – in Excel

    MasterFile.xlsx
    MultipleFilesData.xlsx
    Hi all,

    I need help with appending data from several excel files with the same “data titles” into a single Excel MasterFile. I know there is lot of help/codes out there for simple merge/append, but this one has to extract relevant data and match column/row titles, (some Titles in some files may not be in the same order).

    I want all this data to be brought in a single row in the new Excel MasterFile under appropriate Titles.
    So data from each data file will be arranged under appropriate Column Titles without error.

    Location where files saved are:
    Master excel file is saved in Folder C:\Users\Nostro\Documents\MasterFile
    Multiple excel files are saved in Folder C:\Users\Nostro\Documents\MultipleFiles

    I am attaching the MasterFile (where data needs to be appended); and one sample file (I have more than 200) from where data needs to be extracted.
    I would reapply appreciate help on vba coding or any other appropriate method.

    Thanks.

  2. #2
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    One more detail, in the "MultipleFilesData" file, cell A1 has the fish number (each of the 200 plus files has unique fish number in cell A1).
    This info needs to go in cell A2 in the MasterFile. So, it will be in A2, A3, A4, A5.... as data from consecutive files is appended to the MasterFile.

  3. #3
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    This should do it for you - good luck!
    Please Login or Register  to view this content.
    Last edited by mgs73; 03-09-2017 at 07:00 PM.

  4. #4
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    Quote Originally Posted by mgs73 View Post
    This should do it for you - good luck!
    Please Login or Register  to view this content.

    Thanks mgs73 for the code, I ran it, and it ran good for all the files in the folder. But there is one issue
    From the MultipleFiles, it only extracted data values from Col B. Data from Col C, and Col D was not appended to the MasterData file. Is there anything I need to add (missing code)?
    Thanks again.

  5. #5
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    Oops...

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    Quote Originally Posted by mgs73 View Post
    Oops...

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Thanks for the fast reply.
    So the code is still partially working - it is importing from Col B & Col C, but not col D.
    Also, this time the values imported from each file do not line up in a single row. They go one row below the next in a diagonal fashion.
    Hope you can see what I am talking about by inserting the code in the file I sent above

  7. #7
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    Oops again! The r = r + 1 should've gone after 'Next' before the workbook is closed.

  8. #8
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    Thanks for the edits. So I'm not sure if I'm doing this right now, but am getting the below Debug message:
    Set sh = ThisWorkbook.Worksheets("MasterData")

    So, I open the MasterData excel file where I need the data imported in. These are the steps I am following after opening the file.
    Alt F11; right-click on Sheet1 (under VBAProject (MasterData); Insert; Module. Then paste your code; Ctrl S; select No, and Save as Macro-Enabled File; Then Alt X to close VBA. Then I Run the Macro and the above underlined error pops up.
    VBA code I am using is below (given by you):

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    What error message do you get? Can you upload the workbook?

  10. #10
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    ok my bad, your code worked great, I just forgot to rename Sheet1 to MasterData, and then everything was good.

    We are 75% there. There were still few columns (below) that did not get their data populated:

    Inc_1_Length(mm) Inc_2_Length(mm) Inc_3_Length(mm) Inc_4_Length(mm) Inc_EdgeA_Length(mm) Inc_1b_Length(mm) Inc_2b_Length(mm) Inc_3b_Length(mm) Inc_4b_Length(mm) Inc_EdgeB_Length(mm)

    Length data here for some reason did not pull up from any of the files, I wonder if something is missing again

  11. #11
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    mgs73, would the code be any simpler/easier if I put all the files and the MasterData file in the same folder for convenience?

  12. #12
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    Here is the workbook, and a couple of the multiple files I am extracting the data from, with your code.
    MasterData.xlsm
    GB_40X_109.xlsx
    GB_40X_110.xlsx

  13. #13
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    I ran the code here, and it worked ok. Maybe I've structured the directories different to you?

    C:\Temp\MasterFile\MasterData.xlsm
    C:\Temp\MultipleFiles\GB_40X_109.xlsm
    C:\Temp\MultipleFiles\GB_40X_110.xlsm

  14. #14
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40
    Quote Originally Posted by mgs73 View Post
    I ran the code here, and it worked ok. Maybe I've structured the directories different to you?

    C:\Temp\MasterFile\MasterData.xlsm
    C:\Temp\MultipleFiles\GB_40X_109.xlsm
    C:\Temp\MultipleFiles\GB_40X_110.xlsm

    Were you able to get the last columns to populate with data?

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract data – Multiple Files – to Single File – in Excel

    If the sheet names in the folder is common...
    try
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Extract data – Multiple Files – to Single File – in Excel

    Were you able to get the last columns to populate with data?
    No - I see why now. When I wrote
    Please Login or Register  to view this content.
    I was only expecting one '_'. So when it gets to 'Inc_4b_Length(mm)' it splits it into:

    FindString(0) = Inc
    FindString(1) = 4b
    FindString(2) = Length(mm)

    Would you be able to replace the separating '_' in the headings with a pipe (i.e. '|')?

    I've done that in the attached workbook - hope it works for you!
    Attached Files Attached Files
    Last edited by mgs73; 03-10-2017 at 12:35 AM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract data – Multiple Files – to Single File – in Excel

    Formula adjusted for the heading with multiple underscores.
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    02-06-2013
    Location
    Texas
    MS-Off Ver
    Excel 2019, 365
    Posts
    40

    Re: Extract data – Multiple Files – to Single File – in Excel

    Thanks to mgs73 and jindon , it worked great!!
    Last edited by Nostradamus; 03-11-2017 at 07:36 PM.

+ 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. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 03:34 AM
  2. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 12:36 AM
  3. Extract Data From Multiple Excel Files -> One File
    By ozzabozza in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2013, 10:20 AM
  4. [SOLVED] Extract data from multiple files into a main file
    By yauchildchew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2013, 11:03 PM
  5. [SOLVED] Pull data from the same cell across multiple csv files and place into a single excel file
    By rbmrrbmr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2013, 10:53 AM
  6. Extract Data From Multiple Excel Files -> One File
    By Michael via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:02 PM
  7. Replies: 1
    Last Post: 01-27-2010, 03:06 AM
  8. Replies: 0
    Last Post: 06-17-2008, 12:39 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