Linking to multiple CSV files and renaming tables without ".CSV"

[OneDayOver]

Hi All

I have been using the following macro to link to multiple CSV files in a folder.

Would anyone know how to change it so that the linked tables don't include the ".CSV" part of the file name?

Many thanks

Sub Link_To_Excel_Test()
'Macro Loops through the specified directory (strPath)
'and links ALL Excel files as linked tables in the Access
'Database.

Const strPath As String = "C:\TABLES\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & link to Access
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acLinkDelim, , _
strFileList(intFile), strPath & strFileList(intFile), True, ""
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Linked"
End Sub

 

[Gasman]

@Mark_ wrong thread?

No, @Mark_ replied to post #18 ?
@txab resurrected a 2 year old thread.

 

[CJ_London]

Oh yeah

 

[Mark_]

That's what I get for not checking each posts submission date...

 

[Gasman]

That's what I get for not checking each posts submission date...

Not your fault. I work from Latest posts, so that would come up for me as the thread had been resurrected.
I myself would generally just read that last one, and would likely be caught out as well.