Just feeling my way with VBA...

[Tommy B]

Hi Guys,

Just getting into vba and I have the following problem:

I have written the following code whose purpose is to look in a directory for specific text files and import them if found, if they aren't there I want a msgbox to appear telling the user which files are missing. This works fine with my code up to a point, however the code will stop while it awaits a response to the msgbox (clicking OK), then it will run the next import. Is there a way I can get it to continue with the next routine regardless of whether the user has clicked OK on the mesgbox if the first file (tatracker) is not present....any manna from above greatfully recieved

Here's my begginers code.

Function import_leads()
' Imports hotleads file from C:\leads_folder\hotleads and appends to hotleads_temp_tbl
On Error GoTo import_leads_Err

If strfisfile("c:\leads_folder\hotleads\TATracker.txt") Then
DoCmd.TransferText acImportDelim, "hotleads_spec", "hotleads_temp_tbl", "C:\leads_folder\hotleads\tatracker.txt", True, ""
Else
MsgBox "TA Tracker file not found!", vbExclamation
End If

If strfisfile("c:\leads_folder\hotleads\PBTracker.txt") Then
DoCmd.TransferText acImportDelim, "pbhotleads_spec", "pbhotleads_temp_tbl", "C:\leads_folder\hotleads\pbtracker.txt", True, ""
Else
MsgBox "PB Tracker file not found!", vbExclamation
End If

import_leads_Exit:
Exit Function

import_leads_Err:
MsgBox Error$
Resume import_leads_Exit

End Function

Cheers,

Tommy B

 

[fuzzygeek]

One of several ways to do it

Use this code as an example of how to do it. There may be simplier ways.

Dim booTA As Boolean
Dim booPB As Boolean
Dim strMsg As String
Const strTA As String = "TA Tracker file not found!"
Const strPB As String = "PB Tracker file not found!"
booTA = False
booPB = False


If strfisfile("c:\leads_folder\hotleads\TATracker.txt") Then
DoCmd.TransferText acImportDelim, "hotleads_spec", "hotleads_temp_tbl", "C:\leads_folder\hotleads\tatracker.txt", True, ""
Else
booTA = True
End If

If strfisfile("c:\leads_folder\hotleads\PBTracker.txt") Then
DoCmd.TransferText acImportDelim, "pbhotleads_spec", "pbhotleads_temp_tbl", "C:\leads_folder\hotleads\pbtracker.txt", True, ""
Else
booPB = True
End If

If booTA And booPB Then
strMsg = strTA & vbCrLf & strPB
ElseIf booTA Then
strMsg = strTA
ElseIf booPB Then
strMsg = strPB
Else
strMsg = "NADA"

End If
If strMsg <> "NADA" Then
MsgBox strMsg, vbExclamation
End If

 

[Tommy B]

Nice one

Thanks a million Fuzzygeek...I just couldn't get my head around it, your example has shown me the light...lol!

I will give it a go now.

Once again thank-you for taking the time out to help!

Cheers,

Tommy B