get drive's name - Copy files CD to Harddrive

[iknowu99]

So far I have the following and it works:

Sub mCopyDir()
Dim oFso As Object
Dim sDestDir As String

Const OverWriteFiles = True

'// Copies All files to this Folder
sDestDir = "C:\ATesting\"
'MkDir sDestDir
    
Set oFso = CreateObject("Scripting.FileSystemObject")

TryAgain:
On Error GoTo FsoErr:
'// Note this Overwrite existing files unless set to false!
'// Fso.CopyFolder "C:\AData\", "C:\AATmp\",False
oFso.CopyFolder "D:\*.*", sDestDir, OverWriteFiles


Exit Sub

FsoErr:
If Err.Number = 76 Then
    MkDir sDestDir
    Err.Clear
    Resume TryAgain
Else
    MsgBox "Err#:=" & Err.Number & vbCr & _
        "Descrp:=" & Err.Description, _
        vbMsgBoxHelpButton, _
        "FileSystemObject Error", _
        Err.HelpFile, _
        Err.HelpContext
End If
End Sub

Thanks to Ivan the terrible.

when a cd is inserted the drive's name changes. How does one get the cd name?

 

[iknowu99]

*bump*

 

[iknowu99]

Perhaps MrExcel can suggest how to get file names?

On a similar topic i found a nice function:
http://www.excelguru.ca/node/30