Determine drive

[WLMPilot]

Is there a way to determine what drive is assigned to my memory stick.
Instead of having to "explore", then drag and drop my spreadsheets between my
computer and memory stick, I want to use a command button to save it to the
memory stick.

Thanks,
Les

 

[Gary''s Student]

Double-click My Computer

 

[WLMPilot]

Ok, maybe I was not clear enough. I want to know if there is a code in VBA
(Excel) that can automatically determine what drive letter is assigned to the
memory stick so when I click the command button (Caption="Save To Memory
Stick"), it will know what drive to send it to.

Les

 

[Gary''s Student]

See:

http://groups.google.com/group/comp...q="list+drives"+group:*Excel#c6dbc9d3de889e96

I have not used any of this code.

 

[Dave Peterson]

You could search the *scripting* newsgroups via google:

I found this post:
http://groups.google.co.uk/group/mi...humb+drive+group:*scripting*#b590dbdfcb919660

That pointed to:
http://groups.google.co.uk/group/mi...grammer.wmi/msg/f1f4ee606f2c2069?dmode=source
and
http://groups.google.co.uk/group/mi...ng.vbscript/msg/05aa9d3dd1c7c162?dmode=source

 

[urkec]

You could search the *scripting* newsgroups via google: ....
That pointed to:
http://groups.google.co.uk/group/mi...grammer.wmi/msg/f1f4ee606f2c2069?dmode=source
and
http://groups.google.co.uk/group/mi...ng.vbscript/msg/05aa9d3dd1c7c162?dmode=source

I think that sample code does not distinguish between memory sticks and
external disks. Maybe it would be simpler to rename the memory stick (if
possible) and check for volume name, something like this:

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set colDrives = fso.Drives

For Each objDrive In colDrives
If objDrive.IsReady Then
If objDrive.VolumeName = "MYUSBDRIVE" Then
Debug.Print objDrive.Path, _
objDrive.VolumeName, _
objDrive.DriveType
End If
End If
Next

or using WMI:

Set WMI = GetObject _
("WinMgmts:Root\Cimv2")

Set colLogicalDisks = WMI.ExecQuery _
("Select * From Win32_LogicalDisk " & _
"Where VolumeName = 'MyUSBDrive'")

For Each objLogicalDisk In colLogicalDisks
Debug.Print objLogicalDisk.DeviceID, _
objLogicalDisk.VolumeName, _
objLogicalDisk.Description
Next