I tried something along the lines of this, but haven't found any success.

Option Explicit

Sub ImportCSVsWithReference()
'Author: Jerry Beaucaire
'Date: 11/3/2011
'Summary: Import all CSV files from a folder into a single sheet
' adding a field in row 1 for listing the CSV filenames

Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath As String: fPath = "H:\Renewable" '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)
'insert row 1 and add CSV name
Rows(1).Insert xlShiftDown
Range("A1") = ActiveSheet.Name
'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


If there is an easier way to handle this, please let me know.