Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Error handling for Import TransferText Macro

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!
0
marellano
Asked:
marellano
  • 3
  • 2
1 Solution
 
thenelsonCommented:
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.
0
 
marellanoAuthor Commented:
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, ""


TransferTextTest_Exit:
    Exit Function

TransferTextTest_Err:
    MsgBox Error$
    Resume TransferTextTest_Exit

End Function

Open in new window

0
 
thenelsonCommented:
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" 
Else 
   DoCmd.TransferText acImportDelim, "xpn trigger import spec", "TRIGGER_IMPORT_MASTER", strPathName , False
End If 
End Function 

Open in new window

0
 
marellanoAuthor Commented:
Great! worked like a charm.

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

Happy computing!

Nelson
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now