Hi,
I'm going to be using an Access Database to install files for another
database. I'm going to be using the copy method to copy files from a media
(CD or floppy) to c:\program files\
The trouble is, the drive letter for the media changes depending on the
computer, so it might be a:\ on one and e:\ on another. Is there a way to
find out the path from where I'm copying the files from to use in VBA?
Thanks

Provided the path (less the drive letter) to a file (any one will do) on the
CD or floppy is not also a valid path on any other drive one way would be to
loop through all possible drive letters from a to z and see if the file is
found. Use a function such as this:

Function GetDriveLetter(strFile As String)

Dim strDriveLetter As String
Dim n As Integer

For n = 0 To 25
strDriveLetter = Chr(97 + n)
On Error Resume Next
If Dir(strDriveLetter & ":" & strFile) <> "" Then
If Err = 0 Then
GetDriveLetter = strDriveLetter
Exit For
End If
End If
Next n

End Function

So if you had a file on the CD or floppy with a path <unknown drive
letter>:\myfolder\myfile.txt say you can call the function with:

GetDriveLetter("\myfolder\myfile.txt")

Ken Sheridan
Stafford, England

"s4" wrote:

> Hi,
> I'm going to be using an Access Database to install files for another
> database. I'm going to be using the copy method to copy files from a media
> (CD or floppy) to c:\program files\
> The trouble is, the drive letter for the media changes depending on the
> computer, so it might be a:\ on one and e:\ on another. Is there a way to
> find out the path from where I'm copying the files from to use in VBA?
> Thanks

Provided the path (less the drive letter) to a file (any one will do) on the
CD or floppy is not also a valid path on any other drive one way would be to
loop through all possible drive letters from a to z and see if the file is
found. Use a function such as this:

Function GetDriveLetter(strFile As String)

Dim strDriveLetter As String
Dim n As Integer

For n = 0 To 25
strDriveLetter = Chr(97 + n)
On Error Resume Next
If Dir(strDriveLetter & ":" & strFile) <> "" Then
If Err = 0 Then
GetDriveLetter = strDriveLetter
Exit For
End If
End If
Next n

End Function

So if you had a file on the CD or floppy with a path <unknown drive
letter>:\myfolder\myfile.txt say you can call the function with:

GetDriveLetter("\myfolder\myfile.txt")

Ken Sheridan
Stafford, England

"s4" wrote:

> Hi,
> I'm going to be using an Access Database to install files for another
> database. I'm going to be using the copy method to copy files from a media
> (CD or floppy) to c:\program files\
> The trouble is, the drive letter for the media changes depending on the
> computer, so it might be a:\ on one and e:\ on another. Is there a way to
> find out the path from where I'm copying the files from to use in VBA?
> Thanks