Error handling for Import TransferText Macro

Posted on 2009-12-23
Last Modified: 2013-11-27
I have a Macro that uses TransferText to import a specific text file name.  If the file does not exist, i want the Macro to halt/quit/ PRIOR to the TransferText attempting the import and erroring out.  Basically, I need to validate that the file exists before TransferText attempts to execute.  Is there an Action available for this?

Note, TranferText looks for a file name with with the following property as the import file is labeled with a date format:

="\\filepath\FileName" & Format(Date()-1,"mmddyy") & ".TXT"

Thanks in advance!
Question by:marellano
    LVL 39

    Expert Comment

    You cannot do this in a macro, you can do this in VBA. Convert the macro to VBA and post the code for help on that.

    Author Comment

    Hi thenelson...thanks for the quick response. Here's the coverted macro to VBA:

    ' TransferTextTest
    Function TransferTextTest()
    On Error GoTo TransferTextTest_Err
        DoCmd.TransferText acImportDelim, "xpn trigger import spec", "TRIGGER_IMPORT_MASTER", "\\server2\IT\Databases\XPN\from_xpn\C01398P01NTC" & Format(Date - 1, "mmddyy") & ".TXT", False, ""
        Exit Function
        MsgBox Error$
        Resume TransferTextTest_Exit
    End Function

    Open in new window

    LVL 39

    Accepted Solution

    Leaving in only the necessary stuff:

    Function TransferTextTest()
    Dim strPathName As String 
    strPathName = "\\server2\IT\Databases\XPN\from_xpn\C01398P01NTC" & Format(Date - 1, "mmddyy") & ".TXT"
    'Dir returns zero length string if file not found:
    If Len (Dir (strPathName) & "") = 0 Then 
       MsgBox "File not found" 
       DoCmd.TransferText acImportDelim, "xpn trigger import spec", "TRIGGER_IMPORT_MASTER", strPathName , False
    End If 
    End Function 

    Open in new window


    Author Closing Comment

    Great! worked like a charm.

    Thank you!!
    LVL 39

    Expert Comment

    You're welcome.  Glad to help and thank you very much for the points with "A" grade!

    Happy computing!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    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…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now