View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default Identify CD-Rom drive

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