I need some advice on how to import multiple .CSV files in a directory or a drive folder?

Okay,
Basic information that I feel will be important to solving this issue.

1. I am using a redlion production datalogger that is writing a new .csv file daily it collects data every hour and writes it to an IP directory (HTML) and dumps every 24 hours to a folder on a local server. At both of these location the file type is a .csv. Example of the files would be as follows first link\14012900.csv second link\14013000.csv third link\14013100.csv etc...

2. I want to create an .xlsm(macro enabled workbook) I intend to manipulate the data once imported to excel. I want this .xlsm to refresh upon opening and every few minutes. I want the excel spreadsheet to be able to import each file that is created daily without having interaction with the spreadsheet user.
*I have tried to create a "From Text" data connection referencing the link\*.csv and this only will open the first file created and none of the others on the HTML directory yet it opens none of the files if I use the path to the drive folder.

3. I am currently writing a macro using a for each loop to try and get the data I need every day but this is proving time consuming and there must be an easier and more simple option for this.

Note: I have been told that an SQL server is not an option, even though I think this may be our easiest route of attack.

I appreciate all responses whether they are a solution or even just some pointers that might be some help, Thanks!!!

So I thought it would only be fair to post the final working code for my intentions. The response from mehmetcik was invaluable in getting me going in the right direction I did end up making some minor adjustments here and there to meet my needs but the core coding is the same. I may take this another step further as of right now I am deleting all content and refreshing each time the spreadsheet is open this takes some time and will only get worse as the data in the folder begins to compile. I may decide to find a way to log only new files each day or something but for now this is working. Anyone have any ideas on how to keep yesterdays data saved and continue to pull in new data without rewriting every day?

Sub Manipulate_CSV_C40()

Dim sPath As String, sName As String
Dim bk As Workbook, r As Range
Dim r1 As Range, Sh As Worksheet
Dim ROWS As Integer
Dim lastRow As Integer

sPath = "Y:\productivity log\logs\aba40lot"
'MsgBox (sPath)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

On Error Resume Next
'Workbooks.Add
'ChDir sPath
'ActiveWorkbook.SaveAs Filename:="Summary.xlsx" _
', FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

5 On Error GoTo 0

Sheets("C40_Lot").Select

Cells.Select
Selection.ClearContents


Set Sh = ActiveSheet ' this is the summary sheet

sName = Dir(sPath & "\" & "*.CSV")

10 If sName = "" Then GoTo 20
Set bk = Workbooks.Open(sPath & "\" & sName)

Set r = bk.Worksheets(1).Range("A1").CurrentRegion
Set r1 = Sh.Cells(Sh.ROWS.Count, 1).End(xlUp)(2)
r.Copy
r1.PasteSpecial xlValues
bk.Close SaveChanges:=False

sName = Dir()
GoTo 10

20 ' No More CSV Files so break out of Loop

ActiveWorkbook.Save

Application.ScreenUpdating = True

'Format columns
Cells.Select
Selection.ROWS.AutoFit
Selection.Columns.AutoFit
Columns("A:A").Select
Range("A55").Activate
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Columns("B:B").Select
Range("B55").Activate
Selection.NumberFormat = "h:mm:ss;@"
Cells.Select
Range("A55").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Range("A1").Select

End Sub