Import txt file into Access Table

[redbull]

Hello, I have around 30 reports that run daily. These reports are always formatted the same... XXX then current date. I am able to import a single file just fine, but when trying to apply the date formatting it gives
Run-Time Error '31519':
You cannot import this file


\/ this works

Private Sub cmdImport_Click()
Dim FilePath As String


FilePath = "C:\TroubleList\Atlanta\skb 05-01-2012.txt"
DoCmd.TransferText acImportFixed, "impTroubleReport", "tblTrouble", FilePath, False
MsgBox "Import Complete"

I have googled this issue and found a few answers, however still running into issues... the solutions I have tried..Still giving the error.
\/

Dim FilePath As String
Dim FileDate As Date


FileDate = Format(Now, "mm-dd-yyyy")

FilePath = "C:\TroubleList\Atlanta\skb" & FileDate & ".txt"""
FilePath = "C:\TroubleList\Atlanta\skb" & Format$(Date, "mm-dd-yyyy") & ".txt"""

 DoCmd.TransferText acImportFixed, "impTroubleReport", "tblTrouble", FilePath, False


 MsgBox "Import Complete"

Hopefully its a simple issue and thats why I can't find much on google. Any advice would be great

[June7]

Not sure if this is part of the issue but Format function results in a string value, not a date. Don't declare FileDate as a Date, String will do because that's really how you are using it. If you want a space between skb and the date, need to include it in the literal string part of the FilePath. I think the real issue is the triple quotes, which is causing a literal quote mark to become part of the file name.
FilePath = "C:\TroubleList\Atlanta\skb " & FileDate & ".txt"

[ssanfu]

I see 3 problems....

1) there are too many quotes after ".txt" there should only be one

These reports are always formatted the same... XXX then current date.

2) from the code example that runs, it looks like there is a space between the 'xxx' and the date. You are missing the space in the second example.

3) is the date always like this "05-01-2012" or like this "5-1-2012"? The month and day two characters?


Try this:

   Dim FilePath As String
   Dim FileDate As String

   FileDate = Format(Date, "mm-dd-yyyy")
   FilePath = "C:\TroubleList\Atlanta\skb " & FileDate & ".txt"

   DoCmd.TransferText acImportFixed, "impTroubleReport", "tblTrouble", FilePath, False


   MsgBox "Import Complete for file: '" & Right(FilePath, 18) & "'"

or you can use

   Dim FilePath As String

   FilePath = "C:\TroubleList\Atlanta\skb " & Format(Date, "mm-dd-yyyy") & ".txt"

   DoCmd.TransferText acImportFixed, "impTroubleReport", "tblTrouble", FilePath, False

   MsgBox "Import Complete for file: '" & Right(FilePath, 18) & "'"

[redbull]

   Dim FilePath As String   Dim FileDate As String   FileDate = Format(Date, "mm-dd-yyyy")   FilePath = "C:\TroubleList\Atlanta\skb " & FileDate & ".txt"   DoCmd.TransferText acImportFixed, "impTroubleReport", "tblTrouble", FilePath, False   MsgBox "Import Complete for file: '" & Right(FilePath, 18) & "'"

This worked beautifully, thank you very much! code now has a mention of

'ssanfu and june7 made this import possible, from accessforums.net