Solved

DoCmd.TransferText does not recognize txt file

Posted on 2009-05-13
14
978 Views
Last Modified: 2013-11-27
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
Comment
Question by:kg6lfz
  • 8
  • 5
14 Comments
 
LVL 1

Expert Comment

by:ChrisBroussard
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
There is one in mvps somewhere I think, one here also http://www.tek-tips.com/viewthread.cfm?qid=1238156&page=1
0
 

Author Comment

by:kg6lfz
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 

Author Comment

by:kg6lfz
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 

Author Comment

by:kg6lfz
Comment Utility
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
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
 

Author Comment

by:kg6lfz
Comment Utility
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
 

Author Closing Comment

by:kg6lfz
Comment Utility
Greatly helpful.  Quick and detailed responses are very appreciated.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now