Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2016
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Loop through CSV files, appending selected rows from each to existing Excel file

    Thanks in advance for any help!

    I'm trying to loop through a folder of csv files where in each file the first row is a header followed by a variable number of rows, copy all of the rows below the header, switch to a master Excel file, and append those rows to the bottom of the master.

    I'm using Maudibe's code from another thread to loop through the csv's (which is great), but I'm having trouble with the code to copy and append the data from each csv to the master file. Any help is enormously appreciated!

    Thanks,
    Seth

    Maudibe's open folder of files code:
    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\Users\Maudibe\Desktop\ExcelFiles\"
    Filename = Dir(Path & "*.xlsm")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        '
        ' CODE GOES HERE
        '
         MsgBox Filename & " has opened"
        wbk.Close True
        Filename = Dir
    Loop
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    These lines will select the data.
    LastRow = wkb.Cells(2, 1).End(xlUp).row
    Set rng = wkb.Range("C2:C" & LastRow)

    Then copy the data
    rng.Select
    Selection.Copy

    Paste into the master
    masterwkb.Select
    MasterLastRow = Activesheet.Cells(2, 1).End(xlUp).row + 1
    MasterLastRow.Select
    Selection.Paste

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    sbudick (2016-11-22)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Here are some slight changes needed to paul's clever coding

    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook, masterwbk As Workbook
    Dim Filename As String, rng As Range
    Dim Path As String
    Set masterwbk = ThisWorkbook
    Path = "C:\Users\Maudibe\Desktop\"
    Filename = Dir(Path & "*.csv")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range("A2:A" & LastRow)
    
        'Then copy the data
        rng.EntireRow.Select
        Selection.Copy
    
        'Paste into the master
        masterwbk.Activate
        MasterLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        Cells(MasterLastRow, 1).Select
        ActiveSheet.Paste
        Application.DisplayAlerts = False
        wbk.Close True
        Application.DisplayAlerts = True
        Filename = Dir
    Loop
    End Sub

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    sbudick (2016-11-22)

  6. #4
    New Lounger
    Join Date
    Nov 2016
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is truly phenomenal, thank you both so much! Happy Thanksgiving!

  7. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Quote Originally Posted by Maudibe View Post
    Here are some slight changes needed to paul's clever coding
    You are too kind - I only nicked it from you in the first place. (Did I mention my VBA is a little rusty?)

    cheers, Paul

Posting Permissions

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