CODEWITHSUNDEEP
ms-accessimportvba
Shreyans
Shreyans

Reputation: 55

Importing a file in Access which contains "." in it's name using VBA

I am trying to import a csv file into my access database. It reads like this: "Vol.2016.Aug.23.csv"

When I try to import this using docmd, it gives me an error that it cannot import the file. I figured out the problem that it cannot identify the file as a csv because of the multiple "."

However, I would be getting files named in such a way which I need to import. Is there any way to go about this?

Please help!

Here's my code:

fileName = "Vol." & Year(Date) & "." & MonthName(Month(Date), True) & "." & Day(Date) &  ".csv"

filePath = "C:\Users\House\Desktop\"

DoCmd.TransferText Transfertype:=acImportDelim, TableName:="Table1", fileName:=filePath + fileName, HasFieldNames:=True

Upvotes: 2

Views: 1647

Answers (2)

dbmitch
dbmitch

Reputation: 5386

Okay - this should work for you

I tested on my system and confirmed that your DoCmd call probably uses old MS-ACcess code base requirements and is failing on the "." and any other special chars in the filename - probably something hardcoded to look for the first dot in filename in order to figure out the extension

Solution is to use Short Filenames

Add this to top of your module

' Borrowed code from https://support.microsoft.com/en-us/kb/175512

#If VBA7 Then
    Declare PtrSafe Function GetShortPathName Lib "kernel32" _
      Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
      ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
#Else
    Declare Function GetShortPathName Lib "kernel32" _
      Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
      ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
#End If

Public Function GetShortName(ByVal sLongFileName As String) As String
    Dim lRetVal As Long, sShortPathName As String, iLen As Integer
    'Set up buffer area for API function call return
    sShortPathName = Space(255)
    iLen = Len(sShortPathName)

    'Call the function
    lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
    'Strip away unwanted characters.
    GetShortName = Left(sShortPathName, lRetVal)
End Function

Then modify your code to convert your filename to Short version

Dim filename As String
Dim filePath As String

Dim csvFile As String

filename = "Vol." & Year(Date) & "." & MonthName(Month(Date), True) & "." & Day(Date) & ".csv"

filePath = "C:\Users\House\Desktop\"

' Convert to Short Filename to work with old MS-Access code base
csvFile = GetShortName(filePath & filename)
Debug.Print csvFile

DoCmd.TransferText Transfertype:=acImportDelim, TableName:="Table1", filename:=csvFile, HasFieldNames:=True

Upvotes: 1

Ryan Wildry
Ryan Wildry

Reputation: 5677

Give the following solution a try.

Instead of trying to deal with the periods being present in the filename, I wrote a script to temporarily rename the file name. Basically I replace . with _ to get around the import issue.

Option Explicit

Public Sub ImportTableWithPeriod()
    Dim fso             As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim f               As Object
    Dim FolderName      As String
    Dim Filename        As String
    Dim TempFilepath    As String
    Dim OrigFilePath    As String
    Dim TempFileName    As String
    Dim tempFolderPath  As String

    Filename = "Vol." & Format(Now(), "yyyy") & "." & _
                Format(Now(), "MMM") & "." & _
                Format(Now(), "d") & ".csv"

    FolderName = "C:\Users\House\Desktop\"

    'Build string for replacement names
    'Basically I've replaced the periods with underscores
    TempFileName = Replace(Left(Filename, Len(Filename) - 4), ".", "-") & ".csv"
    TempFilepath = FolderName & TempFileName

    OrigFilePath = FolderName & Filename
    Set f = fso.getFile(OrigFilePath)

    'rename the file temporarily
    f.Name = TempFileName

    'Import the file with the renamed instance
    DoCmd.TransferText Transfertype:=acImportDelim, TableName:="Table1", Filename:=TempFilepath, HasFieldNames:=True

    'Put the name back as it was
    f.Name = Filename

    'Clean up
    set fso = nothing
    set f = nothing
End Sub

Upvotes: 0

Related Questions