+ Reply to Thread
Results 1 to 6 of 6

Merging multiple files into one excel file

  1. #1
    Registered User
    Join Date
    08-29-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    83

    Merging multiple files into one excel file

    Hi,

    Could you please assist with providing a Macro that merges all the data from multiple excel (csv) files and puts the details into a single excel file. The Macro should ignore the top row (row A) when merging from multiple files into the single file.

    Thanks in advance,
    Ash
    Last edited by nobleprince; 07-11-2018 at 05:19 PM.

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Merging multiple files into one excel file

    Have a look at this link
    http://www.eileenslounge.com/viewtopic.php?f=30&t=28622
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    08-29-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Merging multiple files into one excel file

    Thanks the Macro works, but is it possible to edit the macro to ignore the top row from the multiple excel files? please assist with updating the Macro below to reflect this:

    Sub CombineCSVFiles()
    Dim a() As Variant
    Dim sFolder As String
    Dim sFile As String
    Dim i As Long

    With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = False Then Exit Sub
    sFolder = .SelectedItems(1)
    End With
    sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)

    Application.ScreenUpdating = False
    With Sheets("Sheet1")
    i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    sFile = Dir(sFolder & "*.csv")
    Do While sFile <> ""
    a = GetArray(sFolder & sFile)
    .Cells(i, 1).Resize(UBound(a), UBound(a, 2)) = a
    i = i + UBound(a)
    sFile = Dir
    Loop
    End With
    Application.ScreenUpdating = True

    MsgBox "Done...", 64
    End Sub

    Function GetArray(ByVal f As String) As Variant
    Dim a() As Variant
    Dim rw As Variant
    Dim cl As Variant
    Dim txt As String
    Dim i As Long
    Dim ii As Long
    Dim j As Long

    With CreateObject("ADODB.Stream")
    .Charset = "windows-1251"
    .Open
    .LoadFromFile f
    txt = .ReadText
    .Close
    End With

    rw = Split(txt, vbCrLf)
    ReDim a(0 To UBound(rw), 1 To 7)

    For i = 0 To UBound(a) - 1
    cl = Split(rw(i), ",")
    For j = 1 To UBound(a, 2)
    a(i, j) = cl(j - 1)
    Next j
    Next i

    GetArray = a
    End Function

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Merging multiple files into one excel file

    Try this
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-29-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Merging multiple files into one excel file

    Works perfect. Thanks you YasserKhalil.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Merging multiple files into one excel file

    You're welcome. Glad I can offer some help

+ 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. Simple Macro for Merging Excel Files Into One Consolidated File
    By Revitigs in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 11-23-2016, 11:06 PM
  2. Merging multiple files into one excle file with common & different headers
    By gurukiran in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2015, 01:40 AM
  3. Merging cells from multiple files in a folder into one file
    By Alteregoist in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2013, 04:26 AM
  4. Merging multiple Excel files into tabs of one excel file without having to copy and paste?
    By BrettRCourtney in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2013, 03:43 PM
  5. Merging specified worksheet in multiple files into 1 merged file
    By RebeccaEllis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2012, 11:22 AM
  6. Merging multiple Excel files
    By AlexESRI in forum Excel General
    Replies: 4
    Last Post: 08-17-2012, 07:26 AM
  7. Merging multiple excel files into a single doc
    By -------------------- in forum Excel General
    Replies: 1
    Last Post: 01-07-2006, 08:25 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