VBA Importing Text File - Error 3011 - File name has two extensions

[mgilbert86]

Hello,



I am writing VBA module that imports a text file that has two extension names (.tsv.csv).

When the system is trying to find the file, I get error 3011 and it shows me that the file name being searched for stopped at ".tsv."

The microsoft access database could not find the object "FileName20150907.tsv."


Here is the code:

Dim Monday As String


Monday = Forms!Form!Text34

Dim ExtractFile As String
ExtractFile = Dir$("C:\\Folder\FileName" & Monday & "*.tsv.csv")


DoCmd.TransferText acImportDelim, "Spec", "Extract File", ExtractFile, True

Any Ideas on how to fix?

[June7]

Rename the file.

[mgilbert86]

Rename the file.

We have a team that adds a .csv extension on to the files and posts them out to a folder in our shared drive. Problem is, if the user has to go out and rename the file, it would be easier then to just have them manually upload the text file and skip the vba importing the file for them. Is there not any work around within vba to deal with a file with this name?

[JoeM]

We have a team that adds a .csv extension

Can't they alter their process to CHANGE the extension as opposed to adding a new one (seems like a really bad practice)?

If not, you may be come up with VBA script that will do that for you (rename the files first).

[June7]

Options:

1. have the team properly name the file - just have to change the tsv to csv instead of appending csv

2. VBA to rename the file - example of renaming a single file: Name "C:\Temp\MyFile.tsv.csv" AS "C:\Temp\MyFile.csv"
Why do you have * wildcard in the file name? I don't think Access can deal with that in the import operation.