Import all CSV files from a folder into a single sheet

[Rob_excel]

Hi

Was hoping that someone might kindly help me with the following. I found the code below for importing csv files into a single sheet and adapted it slightly. It works great except that if two files have exactly the same column it only keeps one of them while I want all the files imported as they are including duplicate columns. I am new to macros and would really appreciate any help.

Sub ImportCSVsWithReference()
'Author:    Jerry Beaucaire
'Date:      11/3/2011
'Summary:   Import all CSV files from a folder into a single sheet


Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("AQA Big")
Dim fPath   As String:      fPath = "C:\..."    'path to CSV files, include the final \
Dim fCSV    As String
Dim NextCol As Long


If MsgBox("Clear the existing MasterCSV sheet before importing?", _
    vbYesNo, "Clear?") = vbYes Then
        wsMstr.UsedRange.Clear
        NextCol = 1
Else
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If


Application.ScreenUpdating = False  'speed up macro


fCSV = Dir(fPath & "*.csv")       'start the CSV file listing


    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Cells(1, NextCol)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
    Loop
 
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

There is nothing in the macro that I can see which would make any comparison of one .csv file to another, so it should not be omitting any files because they are duplicates. However, if the cell in row 3 is somehow blank, the next file would overwrite the previous file when it copies because of the NextCol variable. The only way to find out for sure would be to manually step throught the procedure using the F8 function key and see what happens when each csv file is copied. Might be a little tedious.