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

Hi NickHK,

There's no question that the code is doing what was requested. Nonetheless,
you seem to have taken exception to my post and made it a pet peeve. I
apologize if that's not the case. I would like to explain myself though!

The first part of my reply was to Chip's post, as a courtesy confirmation to
Chip, with respect to his statement of:

"I don't have a USB drive to test this, so take it as a shot in the dark."

It's quite clear here that he knows it's required for the code to work! So
the second part of my reply was intended for the OP, who may not know it's
required, based on his statement of:

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

which suggests he's looking to query the USBs for a drive. Now WE obviously
understand what he means, but does he? Fact is, he's querying DRIVES, not USB
devices.

Not all USB devices need to be "plugged in" to be enumerated after they've
been installed. Fact is, you can plug the same device, say a printer for
example, into another USB port and Windows will take you down the "Found New
Hardware" journey again, even though the printer was previously installed on
that machine. Now there's 2 printers listed, one for each USB's address. If
you query printers on that system, you'd get both of them listed. If you
query the USB ports on that system, and what devices are assigned them,
you'll get both printers listed.

I didn't feel the OP needed this much info, but I did feel (at the time)
that it might be necessary to point out that the drive must be plugged in. So
what might be "obvious" to some, may not be that to others.

Again, I apologize if I've misread the tone of your persistence with this. I
don't mean to be contentious!

Regards,
Garry

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