Solved

DoCmd.TransferText does not recognize txt file

Posted on 2009-05-13
14
983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 1

Expert Comment

by:ChrisBroussard
ID: 24378408
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
ID: 24378989
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
ID: 24379040
There is one in mvps somewhere I think, one here also http://www.tek-tips.com/viewthread.cfm?qid=1238156&page=1
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:kg6lfz
ID: 24380105
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
ID: 24382883
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
ID: 24382891
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
ID: 24387002
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24389339
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
ID: 24389356
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
ID: 24391110
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
ID: 24393692
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
ID: 24398105
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
ID: 31581155
Greatly helpful.  Quick and detailed responses are very appreciated.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24398378
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

688 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