Hello group,

Is the any code to determine which drive is allocated to the USB
device ?

Michael Singmin

Is the any code to determine which drive is allocated to the USB
device ?

Yes, there is. But you need to use one or two API functions. The code
can be easily found in Excel 2002 Power Programming with VBA by John
Walkenbach. It is a very short code piece but I do not have enough time
to copy it now. Sorry.

Darlove

Hi Michael,
You can try:

Sub RemovableDiskTest()
Const MB& = 1048576
Dim d As Object, Msg$, T#, F#, U#
For Each d In CreateObject("Scripting.FileSystemObject").Drives
With d
If .IsReady And .DriveType = 1 Then
T = .TotalSize / MB: F = .FreeSpace / MB: U = T - F
If Len(Msg) Then Msg = Msg & vbLf & vbLf
Msg = "Drive " & .DriveLetter & ":" & vbTab
Msg = Msg & .VolumeName & " (" & .FileSystem & ")"
Msg = Msg & vbLf & "Total size:" & vbTab
Msg = Msg & Format(T, "#,##0 MB") & vbLf
Msg = Msg & "Free space:" & vbTab
Msg = Msg & Format(F, "#,##0 MB") & vbLf
Msg = Msg & "Used space:" & vbTab
Msg = Msg & Format(U, "#,##0 MB")
End If
End With
Next
If Len(Msg) Then MsgBox Msg, 64
End Sub

MP

"Michael Singmin" a écrit dans le message de news:
...
Hello group,

Is the any code to determine which drive is allocated to the USB
device ?

Michael Singmin

Try the following code. You'll need a reference to the Windows
Scripting RunTime (from VBA, choose Tools, then References.
Select "Windows Scripting RunTime " from the list. I don't have a
USB drive to test this, so take it as a shot in the dark.

Dim FSO As Scripting.FileSystemObject
Dim Drv As Scripting.Drive
Set FSO = New Scripting.FileSystemObject

For Each Drv In FSO.Drives
Debug.Print Drv.DriveLetter, Drv.DriveType
If Drv.DriveType = Removable And Drv.DriveLetter < "A" Then
Debug.Print "Removable" & Drv.DriveLetter
End If
Next Drv


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Michael Singmin" wrote in message
...
Hello group,

Is the any code to determine which drive is allocated to the
USB
device ?

Michael Singmin

Hi Chip,

FWIW: -the code to find the USB removeable drive does work, but only when
it's active (there's a "stick" in it).

Regards,
GS

GS,
Well doesn't that make sense ?
If there's no stick, then it's not a USB drive; merely a USB port.

NickHK

"GS" wrote in message
...
Hi Chip,

FWIW: -the code to find the USB removeable drive does work, but only when
it's active (there's a "stick" in it).

Regards,
GS

NickHK,
Let me clarify:

It detects any port that's used with/as a removeable storage device. Not
just "USB" ports.

Regards,
GS


"NickHK" wrote:

GS,
Well doesn't that make sense ?
If there's no stick, then it's not a USB drive; merely a USB port.

NickHK

"GS" wrote in message
...
Hi Chip,

FWIW: -the code to find the USB removeable drive does work, but only when
it's active (there's a "stick" in it).

Regards,
GS

GS,
Yes, because you are enumerating each drive in the system, not each port.
So the code is doing what was requested.

NickHK

"GS" wrote in message
...
NickHK,
Let me clarify:

It detects any port that's used with/as a removeable storage device. Not
just "USB" ports.

Regards,
GS


"NickHK" wrote:

GS,
Well doesn't that make sense ?
If there's no stick, then it's not a USB drive; merely a USB port.

NickHK

"GS" wrote in message
...
Hi Chip,

FWIW: -the code to find the USB removeable drive does work, but only
when
it's active (there's a "stick" in it).

Regards,
GS

Thanks to you all,

In this Excel group, ask a question and much more than
answers wil be forthcoming.

I have the Walkenbach book mentioned in the first reply and
I found the article.

Thanks,

Michael

GS wrote:

Hi Chip,

FWIW: -the code to find the USB removeable drive does work, but only when
it's active (there's a "stick" in it).

Regards,
GS