• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

DoCmd.TransferText does not recognize txt file

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
Wend
    MsgBox "Completed"    
End Sub

Open in new window

0
kg6lfz
Asked:
kg6lfz
  • 8
  • 5
1 Solution
 
ChrisBroussardCommented:
If you need to pause execution to allow the file to be renamed, look at the DoEvents function.

From VB online help:

DoEvents Function

Yields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

Example
This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.

' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000    ' Start loop.
    If I Mod 1000 = 0 Then     ' If loop has repeated 1000 times.
        OpenForms = DoEvents    ' Yield to operating system.
    End If
Next I    ' Increment loop counter.
 
0
 
rockiroadsCommented:
Have a look at using ShellWait, this shells the process and waits for it to complete before running the next command. Plenty of examples here. I do have a sample function but not on me as my original laptop is busted, Im using a temp one so dont have access to it.
0
 
rockiroadsCommented:
There is one in mvps somewhere I think, one here also http://www.tek-tips.com/viewthread.cfm?qid=1238156&page=1
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
kg6lfzAuthor Commented:
I appreciate your quick responses.  I took some time and searched on DoEvnets and ShellWait, however, I am not sure how to apply those to my code.  I am a very beginner to this.  If you can apply your suggestions to my code, it will be very helpful.  Again, I appreciate it very much.  Thank you.  / hb
0
 
rockiroadsCommented:
Ok, you code as it stands now uses Shell. This spawns the process, lets it run. It does not wait for it to complete.
What you are next doing is a Transfertext. Now if this is dependant on the previous command completing, then you will run into problems if the previous step has not completed.
Using ShellWait function, handles the first call. It spawns the process but waits for it to complete.

Now looking at your code again, all it seems there is no need to use Shell at all. You can use commands like FileCopy instead or continue to use fso.

But curious as to what you are trying to achieve here with

cmd /c ren U:\Import\*.SNIS, Import.txt

cmd /c waits for command then terminates (but shell just issues, doesnt wait for this cmd to complete)
ren is rename - surely you can only rename one file at a time?

The way I would code this is to rename and import as I go thru the list of files




Private Sub cmdImport_Click()
    
    Dim fso As New FileSystemObject
    Dim sFile As String
    Dim sPath As String
    Dim sArchive As String
    
    On Error Resume Next
    
    sPath = "U:\Import\"
    sArchive = "U:\Archive\"
    
    'I HAVENT TESTED YOUR ARCHIVE, IM ASSUMING IT WORKS
    fso.CopyFile sPath & "*.SNIS", sArchive
    
    ChDir sPath
    If err.Number <> 0 Then
        MsgBox "Cannot change to import directory. " & err.Description
        Exit Sub
    End If
    
    'Rename each file and import
    sFile = dir$(sPath & "*.SNIS")
    Do While sFile <> ""
        
        'First rename suffix
        FileCopy sFile, Replace(sFile, ".SNIS", ".txt")
        If err.Number <> 0 Then
            MsgBox "Error Copying to .txt " & err.Description
        Else
            'Delete original
            Kill sFile
            
            'Import
            DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", sPath & sFile, , ""
        End If
        
        sFile = dir
    Loop
    
    Set fso = Nothing
    
    MsgBox "Completed"
End Sub

Open in new window

0
 
rockiroadsCommented:
Probably better to remove the use of fso altogether and copy to archive directory within the loop (again, use FileCopy  eg FileCopy spath & sfile, sarchive & sfile)
0
 
kg6lfzAuthor Commented:
rockiroads -
Thank you very much for your detailed explanations.  I copied your code and executed.  I got an error "Error Copying to .txt  File not found".  I verified it from Windows Explorer.  *.SNIS file did not get renamed to *.txt.  But file copy to Archive folder was successful.  Did I do something wrong?  If you can help me once more, it will be greatly appreciated.  Thank you veyr much.  / hb
0
 
rockiroadsCommented:
So file did not get copied and there was no error whatsoever?
Strange. Ok, gonna tweak this a little

version 1 - diagnostic version, if you run this, it should tell you what directory you are currently in (should be where you files are)
and what files are being changed

Run this and see what happens

Private Sub cmdImport_Click()
    
    Dim fso As New FileSystemObject
    Dim sFile As String
    Dim sPath As String
    Dim sArchive As String
    Dim sNewFile As String
    
    On Error Resume Next
    
    sPath = "U:\Import\"
    sArchive = "U:\Archive\"
    
    'I HAVENT TESTED YOUR ARCHIVE, IM ASSUMING IT WORKS
    fso.CopyFile sPath & "*.SNIS", sArchive
    
    ChDir sPath
    If Err.Number <> 0 Then
        MsgBox "Cannot change to import directory. " & Err.Description
        Exit Sub
    End If
    
    MsgBox "In Dir " & CurDir
    
    'Rename each file and import
    sFile = Dir$(sPath & "*.SNIS")
    Do While sFile <> ""
        
        'First rename suffix
        sNewFile = Replace(sFile, ".SNIS", ".txt")
        MsgBox "Copying " & sFile & " to " & sNewFile
        FileCopy sFile, sNewFile
        If Err.Number <> 0 Then
            MsgBox "Error Copying to .txt " & Err.Description
        Else
            'Delete original
            Kill sFile
            
            'Import
            DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", sPath & sFile, , ""
        End If
        
        sFile = Dir
    Loop
    
    Set fso = Nothing
    
    MsgBox "Completed"
End Sub

Open in new window

0
 
rockiroadsCommented:
version 2, as above except uses full pathnames. Ive a slight feeling it might have something to do with not changing directory. not sure though


Private Sub cmdImport_Click()
    
    Dim fso As New FileSystemObject
    Dim sFile As String
    Dim sPath As String
    Dim sArchive As String
    Dim sNewFile As String
    
    On Error Resume Next
    
    sPath = "U:\Import\"
    sArchive = "U:\Archive\"
    
    'I HAVENT TESTED YOUR ARCHIVE, IM ASSUMING IT WORKS
    fso.CopyFile sPath & "*.SNIS", sArchive
    
    ChDir sPath
    If Err.Number <> 0 Then
        MsgBox "Cannot change to import directory. " & Err.Description
        Exit Sub
    End If
    
    MsgBox "In Dir " & CurDir
    
    'Rename each file and import
    sFile = Dir$(sPath & "*.SNIS")
    Do While sFile <> ""
        
        'First rename suffix
        sNewFile = Replace(sFile, ".SNIS", ".txt")
        MsgBox "Copying " & sPath & sFile & " to " & sPath & sNewFile
        FileCopy sPath & sFile, sPath & sNewFile
        If Err.Number <> 0 Then
            MsgBox "Error Copying to .txt " & Err.Description
        Else
            'Delete original
            Kill sPath & sFile
            
            'Import
            DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", sPath & sFile, , ""
        End If
        
        sFile = Dir
    Loop
    
    Set fso = Nothing
    
    MsgBox "Completed"
End Sub

Open in new window

0
 
kg6lfzAuthor Commented:
Thank you very much for your time and efforts.  I copied your Version 2 code.  File name was renamed from "*.SNIS" to ".txt" successfully.  No error occured.  However, unfortunately, DoCmd.TransferText did not work.  Nothing was imported.  To make sure specifications and table names are correct, I specified file name as U:\Import\improt.txt and changed your code little bit as follows:
DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", "U:\Import\import.txt", , ""
When I did that, import was processed correctly.  Do you know why?
Question:  For Version 2, Line 40, it reads as "sFile".  Is it "sNewFile" since we renamed to TXT?  
FYR, these are results of MessageBox:
Ln23: In Dir C:\Data  - (I renamed "My Documents" as "Data")
Ln31: Copying U:\Import\090513.144738.SNIS to U:\Import\090513.144738.txt  - This time worked perfectly.
I apologize for asking you many times, but I appreciate it very much.  Thank you.  / hb
0
 
rockiroadsCommented:
Yes! Good spot, my apologies. I forgot to change that when I created v1 and v2.

DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", sPath & sFile, , ""

should be sNewFile because that is the file with .txt in it

DoCmd.TransferText acImportFixed, "Import_specs", "tblImport", sPath & sNewFile, , ""

0
 
kg6lfzAuthor Commented:
Finally it worked.  Great.  I appreciate so much that you took some time for me to solve this problem.  It will reduce my burden in the future.  Also, I appreciate that you gave me the new opportunity to learn the codes.  As I learn more, it becomes more interesting.  Again, thank you very much.  / hb
0
 
kg6lfzAuthor Commented:
Greatly helpful.  Quick and detailed responses are very appreciated.
0
 
rockiroadsCommented:
glad you got it going and thanks for the appreciation. I usually do try to explain but a number of times the person asking is not interested, they just want a solution. Good luck with the rest of your project :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now