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

 

[Mark_]

Yeah - that's the sticking point for me. The connection string works when I include the filename but fails without one. The issue is that the CSV filenames change every day and do not exhibit patterns like datetime stamps, etc.

This sounds like an operational issue you are trying to fix with code. Is there a very good operational reason they cannot provide filenames that are consistent?

 

[CJ_London]

@Mark_ wrong thread?

 

[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.