Michel,
In VBA go to the Tools menu, choose References, then scroll down
to Microsoft Scripting Runtime. Put a check next to this and
click OK. Then, you can use code like the following:
Dim FSO As Scripting.FileSystemObject
Dim Drv As Drive
Set FSO = New Scripting.FileSystemObject
For Each Drv In FSO.Drives
If Drv.DriveType = CDRom Then
Debug.Print Drv.DriveLetter
End If
Next Drv
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"MD" wrote in message
. ..
Using FileCopy, I'd like to copy a file from a CD-Rom that is
always in the
same folder to the hard drive.
Sub TranferFiles()
SourceFile = "E:\folder\MyFile.doc"
DestinationFile = "C:\Temp\MyFile.doc"
FileCopy SourceFile, DestinationFile
End Sub
One problem with this....The CD-Rom drive is not always E from
one PC to the
other. So how do I identify the CD-Rom drive so that my code
looks like
CD_Drive = ???????
SourceFile = CD_Drive + "\folder\MyFile.doc"
Regards!
Michel