Using Dir() with removable drives

  • Thread starter Thread starter Paul Johnson
  • Start date Start date
P

Paul Johnson

I am using the built-in Dir() function to check for the existence of a file
when opening the application. The code is simply

If Dir(FilePathAndName) = "" Then
' Code to run when file is not found...

This works fine until, for example, I take the application file from one
machine where "D:" is my thumbdrive and run it on another machine where "D:"
is my CD-ROM, or if the last data file was on a floppy disk in drive "A:"
When the code hits the line with the Dir() statement, it gives a "Disk or
Network Error" message. I guess I could build in error-trapping to work
around that event, but I wonder if there is a more elegant way to get around
this obstacle. Any suggestions?

TIA
Paul Johnson
 
Any code involving i/o operations definately needs error handling.

There are so many things that could go wrong, that you *want* the VBA error
message that says what's wrong, rather than try to code them all yourself.

Other examples include bad file name (e.g. contains "*"), bad path, resource
not available, permission denied, no media in drive, and so on.
 
. I guess I could build in error-trapping to work
around that event, but I wonder if there is a more elegant way to get
around this obstacle. Any suggestions?
Click to expand...

If you use the FileSystemObject object (in the Scripting library, I
think), you can check in the Drives collection to see if it's there. This
is from memory, so treat this with rubber gloves:

Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.DriveExists("d:") Then
Msgbox "There is no D: drive"

ElseIf Not fso.FileExists("D:\externaldata\ImportThis.mdb") then
MsgBox "They forgot to put the file on!"

Else
Call ImportFile("D:\externaldata\ImportThis.mdb")

End If

' clean up
Set fso = Nothing

Hope that helps


Tim F
 
Thank you! This is just what I was looking for. There is so much of VBA
that the Help file does not cover. Is there a good online reference for the
use of this and other similar objects that aren't documented in VBA help?

Paul
 
Is there a good online reference for the
use of this and other similar objects that aren't documented in VBA help?
Click to expand...

Don't know about online; I still like books. My best resource on the FSO,
for example, is VBScript Programmer's Reference from WROX publishing, but
it's probably not a lot different from any number of similar books.

Then again, the very very best reference is here on m.p.a.* !!

All the best


Tim F
 
Back
Top