How to import a CSV file when its name contains dots?

Question

I need to import .csv files via script from a read-only directory to an Access table.

It fails for files with dots in the name: e.g. fi.le.name.csv.

I found these solutions:

1. rename the file
2. copy the file and give it a name without dots

Is it possible to solve it differently?

Dim strSelect as String
Dim strFile as String 
Dim strConnectionString as String
Dim strFolder as String
Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

strFolder = "C:\path"

strConnectionString = "Provider=" & _
  CurrentProject.Connection.Provider & _
  ";Data Source=" & strFolder & Chr(92) & _
  ";Extended Properties='text;HDR=YES;FMT=Delimited'"

cn.Open strConnectionString

strFile = "fi.le.name.csv"
strSelect = "SELECT * FROM " & strFile

rs.Open strSelect, cn, adOpenForwardonly 'and here is the failure

Answer 1

I tried use shortname, it's working for me:

    Sub dots()
    Dim strSelect As String
    Dim strFile As String
    Dim strConnectionString As String
    Dim strFolder As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    
    strFolder = "c:\Users\Alex20\Documents" ' my path
    
    strConnectionString = "Provider=" & _
    "Microsoft.ACE.OLEDB.12.0" & _
    ";Data Source=" & strFolder & _
    "\;Extended Properties='text;HDR=YES;FMT=Delimited'"
    
    'Debug.Print strConnectionString
    
    cn.Open strConnectionString
    
    strFile = "fi.le.name.csv"
    strFile = ShortName(strFile)
    strSelect = "SELECT * FROM `" & strFile & "`"
    
    Debug.Print strSelect   'SELECT * FROM `FILENA~1.CSV`
    rs.Open strSelect, cn, adOpenForwardonly 'and here is the failure
    
    Do While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1)
        rs.MoveNext
    Loop
End Sub

Function ShortName(filespec)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    ShortName = f.ShortName
End Function