I wrote a code to import TXT file to access with vba. First, I need to download a file from FTP, but this process is done separately. The downloaded file has fixed length data, but has extention of "*.SNIS". I want to copy the original (*.SNIS) to Archive folder, then rename to *.TXT and import to Access table. When I click, I don't get error, but nothing gets imported. It seems that DoCmd statement get executed too early before the file is renamed, therefore, DoCmd does not recognize TXT file. In fact, when I open Windows Explorer, the TXT file exits. Do you know why the TXT file is not imported to Access correctly? I appreciate your suggestions in advance. Thak you / hb
Private Sub cmdImport_Click()
Dim fso As New FileSystemObject
fso.CopyFile "U:\Import\*.SNIS", "U:\Archive\"
' U: is network drive.
Call Shell("cmd /c ren U:\Import\*.SNIS, Import.txt", vbNormalFocus)
Set fso = Nothing
Dim txtFile, txtpath
txtpath = "U:\Import"
txtFile = Dir(txtpath & "\*.txt")
While txtFile <> ""
DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", txtpath & "\" & txtFile, , ""
txtFile = Dir