M
Michael Singmin
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 ?
Michael Singmin
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
darlove
Is the any code to determine which drive is allocated to the USB
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
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
M
Michel Pierron
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
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
C
Chip Pearson
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
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
G
Guest
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
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
N
NickHK
GS,
Well doesn't that make sense ?
If there's no stick, then it's not a USB drive; merely a USB port.
NickHK
Well doesn't that make sense ?
If there's no stick, then it's not a USB drive; merely a USB port.
NickHK
G
Guest
NickHK,
Let me clarify:
It detects any port that's used with/as a removeable storage device. Not
just "USB" ports.
Regards,
GS
Let me clarify:
It detects any port that's used with/as a removeable storage device. Not
just "USB" ports.
Regards,
GS
N
NickHK
GS,
Yes, because you are enumerating each drive in the system, not each port.
So the code is doing what was requested.
NickHK
Yes, because you are enumerating each drive in the system, not each port.
So the code is doing what was requested.
NickHK
G
Guest
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
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
M
Michael Singmin
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.