TransferText - Import file has multiple periods

[lakers8176]

Access 2003...I am attempting to import a file into access using the TransferText function but I receive a Runtime Error 3011, MS Jet db engine could not find the file. The file I am looking to import is named "wws070o.la.071016.003100.txt" (the numbers are a date and time stamp that will change daily). I have it in my code and it finds the file and allows me to select the file but it won't import the file. If I remove the periods in the file name (and tweak the .InitialFileName line) it runs fine. I would like to eliminate the end-user intervention of having to rename the file each time they import.

I've tried to manually import the file and I also receive the same error, could not find the file.

Any ideas?

Function PickFile()

Dim fDialog As Office.FileDialog
Dim varFile As Variant
 
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
  .AllowMultiSelect = False
  .Title = "Select a Text File to Import"
  .InitialFileName = "\\cvsftp001\ftproot\mis\wws070a\wws070o.la.*.*.txt"
  
  .Filters.Add "Text Files", "*.TXT"
  
  If .Show Then
    For Each varFile In .SelectedItems
      DoCmd.TransferText acImportFixed, "wws070ola", "wws070ola", varFile
Next
  Else      'Nothing selected in File Dialog
  End If
End With
End Function

 

[Remou]

Why not rename the file via code using Name As?

 

[lakers8176]

It's a 80mb file that is in a central directory and I don't want to premanently rename it. I tried to use the Copy command and it takes forever to copy across our WAN. I can't believe that VB and Access can't handle a file with multiple .'s in it.

 

[Remou]

You can tamper with the registry to allow a wider range of extensions for import:

http://office.microsoft.com/en-us/access/HP030662041033.aspx