Importing File - what to do if not there

[Pele]

In the Access application I had put together, I wrote a
macro linked to a command button to go an import a text
file from a pre-defined directory path and add the file to
a database table.

The problem is, the macro just asks the User as to if they
have the file available. If they say, YES, the macro will
then go ahead and import the text file. The problem arises
if the User says YES BUT if the text file is not available
at the sub-directory. If this happens, the macro just
halts and displays the database window.

I would prefer to add some commands to my macro such that
if the file doesn't exist, the macro should give the
warning and then go back to the Switchboard (instead of
opening the database window; which I don't want the User
to have access to).

Can anybody help. Thanks.

Pele

 

[Nikos Yannacopoulos]

Pele,

In your macro design, make sure the Conditions column is visible; if
not, go View > Condtitions in the menu. Now, insert two new lines at the
point where you want the check done, and put this expression in the
conditions column of the first new line:

Dir("Your Path and file name here") <>""

and a msgbox action net to it, with the message of your liking. In the
second new line, put three dots (ellipsis sigh) in the Conditions
column, which tells Access that this line will be executed subject to
the condition in the previous line, and next to it an action StopMacro.

so, if the file is not found, the user gets an approrpiate error
message, and the rest of the macro lines are not executed.

HTH,
Nikos

 

[Guest]

You can also pop-up a file picker that requires the user to point to the
needed file:

Sub UseFileDialogPicker()

Dim lngCount As Long

'Open the file dialog
With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.Show

'Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
End With
End Sub

HTH

 

[Guest]

I forgot to mention in the previous post that you need to make a reference to
the

Microsoft Office XX.x Object Library for the "picker" to work...

 

[Guest]

The following function will tell you if a file exists, but this also presumes
that you know the full file name (i.e. drive, path, name, extension):

Public Function FileExists(argFullName As String) As Boolean
'RETURNS TRUE IF THE FILE EXISTS
FileExists = Len(Dir(argFullName)) > 0
End Function

The above function returns TRUE if the file exists, FALSE if not. Use it by
calling it as in the following example:

Public Sub DoesFileExist()
Dim sFullName As String
sFullName = "c:\NorthWind.mdb"
If FileExists(sFullName) = True Then
MsgBox "Its there!"
Else
MsgBox "Its not there!"
End If
End Sub

HTH

 

[Pele]

Thanks for your help... will be trying this.

-----Original Message-----
Pele,

In your macro design, make sure the Conditions column is visible; if
not, go View > Condtitions in the menu. Now, insert two new lines at the
point where you want the check done, and put this expression in the
conditions column of the first new line:

Dir("Your Path and file name here") <>""

and a msgbox action net to it, with the message of your liking. In the
second new line, put three dots (ellipsis sigh) in the Conditions
column, which tells Access that this line will be executed subject to
the condition in the previous line, and next to it an action StopMacro.

so, if the file is not found, the user gets an approrpiate error
message, and the rest of the macro lines are not executed.

HTH,
Nikos



.

 

[Pele]

Thanks...

-----Original Message-----
You can also pop-up a file picker that requires the user to point to the
needed file:

Sub UseFileDialogPicker()

Dim lngCount As Long

'Open the file dialog
With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = True
.Show

'Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
End With
End Sub

HTH


.

 

[Guest]

Private Sub trtxt()
On Error GoTo GoTotrtxt 'If you get an error like no table, no file, not
spec, or wrong spec.
DoCmd.TransferText acImportFixed, "Import Specificaiton Name",
"TableName", "C:\temp.txt"
Exit Sub 'Stop the sub if no errors
GoTotrtxt: 'Pop up a message box.
MsgBox "Please validate the file name directory and extension. The
expected file c:\temp.txt was not found. In some instances the table and
import specificaitons may be missing. ", vbYesNo, "File not found where
expected"

End Sub