+ Reply to Thread
Results 1 to 21 of 21

Macro to read the multiple csv files and consolidate into single excel

  1. #1
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Macro to read the multiple csv files and consolidate into single excel

    Hello All, This is my first post in this thread, and I am new to VBA as well so I need the help from all of you guys.

    What I want to do is

    I have one excel file name Result.xlsx, which contains two Sheets named Sheet1 and Sheet2.
    What I want to do is the sheet2 should read all csv files from a particular location(one by one), and it should consolidate the data into single that is put all the csv's files data into Sheet2.

    Please help

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

    Re: Macro to read the multiple csv files and consolidate into single excel

    you can arrange this
    Please Login or Register  to view this content.
    Last edited by patel45; 01-01-2013 at 10:02 AM.
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    Another method. Should be speedy if you have a lot of files to consolidate.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    When I am executing this It shows the error, The error is
    Run-time error '1004'
    Application-defined or object-defined error

  5. #5
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    Sorry, This thread works But I am getting a issue- The data is the csv files are like this

    ESP Client,ESP Engagement,Misc_Projects_120101,DEFAULT,HA,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected-Done,"she,joy.",200111,Unknown,Full Time,,Delivery_DONE AMO,Approved,2012-12-03,2012-12-06,2012-12-06,"Occupied Hours
    (0)",0,"Approved Hours
    (112)",8,"Pending Hours
    (0)",0,"Pending and Approved Hours
    (112)",8,

    ESP Client,ESP Engagement,Misc Projects_120101,DEFAULT,HR,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected - Pending,"she, aj v.",200111,Unknown,Full Time,,Delivery_Pending AMO,Approved,2012-12-04,2012-12-14,2012-12-14,"Occupied Hours
    (0)",0,"Approved Hours
    (112)",8,"Pending Hours
    (0)",0,"Pending and Approved Hours
    (112)",8,



    But I want the output to be written in the consolidated sheet is in a single line

    ESP Client,ESP Engagement,Misc_Projects_120101,DEFAULT,HA,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected-Done,"she,joy.",200111,Unknown,Full Time,,Delivery_DONE AMO,Approved,2012-12-03,2012-12-06,2012-12-06,"Occupied Hours (0)",0,"Approved Hours(112)",8,"Pending Hours(0)",0,"Pending and Approved Hours(112)",8,

    ESP Client,ESP Engagement,Misc Projects_120101,DEFAULT,HR,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected - Pending,"she, aj v.",200111,Unknown,Full Time,,Delivery_Pending AMO,Approved,2012-12-04,2012-12-14,2012-12-14,"Occupied Hours(0)",0,"Approved Hours(112)",8,"Pending Hours(0)",0,"Pending and Approved Hours(112)",8,

    Means to say this as single lines from ESP Client(1st column) till 8(last columns in a single columns)
    Last edited by parthmittal2007; 01-02-2013 at 12:11 AM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    There are two macro codes. Which code errors and which code works but has the wrong format?

    I don't understand what the output format should look like (post #5)?
    Do you want all the consolidated text in one column and not separated into columns for each comma field?

  7. #7
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    I have manipulated my csv sheet. Can you please do me one more favour.
    Does the macro can perform an action that is
    In Sheet2 before consolidating the data from different csv files, if prior any data is present in sheet 2 it should delete all of that..

    Thanks once again for your wonderful post

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 01-02-2013 at 12:45 AM.

  9. #9
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    Can the location be fixed, means to say it should not ask the location, It should take location of csv's mentioned in the code.

  10. #10
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    And one more question Does this macro will run , when we open the excel sheet.??, If not then how we can do that..

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    You're welcome.

    Change the fixed path to suit.

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    Quote Originally Posted by parthmittal2007 View Post
    And one more question Does this macro will run , when we open the excel sheet.??, If not then how we can do that..
    Put this in the ThisWorkbook code module.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    When opening a work book, The macro does not run automatically, The data does not change.When I run the macro manually then only data changes

    It gives error
    Compile Error:
    Sub or function not defined
    Last edited by parthmittal2007; 01-02-2013 at 01:54 AM.

  14. #14
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    Try this. Put the two macros below in the same code module. Delete the previous Private Sub Workbook_Open macro.

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    It is not giving error, But it also not refreshing, still I have to do it manually.

    Thanks for your fantastic logic once again.

  16. #16
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Macro to read the multiple csv files and consolidate into single excel

    It works for me. Not sure what to tell you.

    You cannot change the name of the Auto_Open macro: Private Sub Auto_Open
    The only thing it does is call the other macro when you open the workbook.

    Are both macros located in Module1?

  17. #17
    Registered User
    Join Date
    01-02-2013
    Location
    ind.
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to read the multiple csv files and consolidate into single excel

    its a superb code!

    friend can you help me as well?

    i want to open every file from selection to be opened in a new sheet.

    for example:
    if i open 10 files then ten sheets should be opened on my workbook.

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

    Re: Macro to read the multiple csv files and consolidate into single excel

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    DONE Its completed
    Last edited by parthmittal2007; 01-02-2013 at 09:31 AM.

  20. #20
    Registered User
    Join Date
    01-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Macro to read the multiple csv files and consolidate into single excel

    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    01-02-2013
    Location
    ind.
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to read the multiple csv files and consolidate into single excel

    Thanks you so much its working. But i my facing two hurdles in it.

    1.when i am running this code its open only 1 sheet with below error.
    Run time Error 91
    Object variable or with block not set.
    on next attempt it opens all selected.

    2. its changing the dateformat of my data from dd-mm-yyyy to mm-dd-yyyy.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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