I want excel to backup sheet "Record" using code but only onto a Pendrive
when a workbook is close.
The path of any removable drive may change from time to time.
Can excel extract the path names of only removable drives at any one time,
or can i somehow extract an 'identifier' on the pendrive.

Thanks guys and girls, for all your help so for. Tt's been productive.

Sunil

Uniquely identify your drive with some dummy file in the root folder


Private Declare Function GetDriveType Lib "kernel32" _
Alias "GetDriveTypeA" (ByVal nDrive As String) As Long
Private Const DRIVE_REMOVABLE As Long = 2
'Private Const DRIVE_FIXED = 3
'Private Const DRIVE_REMOTE = 4 ' eg network
'Private Const DRIVE_CDROM = 5
'Private Const DRIVE_RAMDISK = 6

Sub testGetDrive()
Dim sDrive As String
Const cKNOWNFILE As String = "unique_file.txt" ' << change

If GetDrive(sDrive, cKNOWNFILE) Then
MsgBox sDrive
Else
MsgBox "not found"
End If

End Sub

Function GetDrive(sDrive, sFile As String) As Boolean
Dim i As Long
For i = Asc("D") To Asc("Z")
sDrive = Chr(i) & ":\"
If GetDriveType(sDrive) = DRIVE_REMOVABLE Then
If FileExists(sDrive & sFile) Then
GetDrive = True
Exit Function
End If
End If
Next
sDrive = ""
End Function

Private Function FileExists(ByVal sFile As String) As Boolean
Dim nAttr As Long
On Error Resume Next
nAttr = GetAttr(sFile)
FileExists = (Err.Number = 0) And ((nAttr And VBA.vbDirectory) = 0)
End Function

Regards,
Peter T

"sunilpatel" wrote in message
...
I want excel to backup sheet "Record" using code but only onto a Pendrive
when a workbook is close.
The path of any removable drive may change from time to time.
Can excel extract the path names of only removable drives at any one time,
or can i somehow extract an 'identifier' on the pendrive.

Thanks guys and girls, for all your help so for. Tt's been productive.

Sunil