write usb flash drive information in to excel cell

[pcbpradeep]

Hi all. I am very new to forum if I am posting in wrong place pleas
guide me.
I am a technical person but not good with vb etc I have done bit work i
formulas.

1. I am writing one excel 2003 based calculator
2. Excel file will be in removable normal USB flash drive

I want below things to be written in individual cells of the same excel

1. Size of the USB STORAGE.
2. Manufacturer or make of storage device
3. Hardware ID of the USB device
4. Serial number of the USB

Other than the above if any more information can be written it will b
more useful.

Let me know any one can help me out please

Pradee

 

[GS]

Look into working with WMI using the "\root\CIMV2" namespace. The info
you want is available but requires running queries in several WMI
classes so that you're sure you're polling the correct USB device...

Win32_LogicalDisk
Query this class filtering only USB drives via
DriveType = "1" (RemoveableDisk).
Make sure you check its ID matches its drive letter (Path).
Note that the device.ID is its system descriptor (drive assigned
when it got plugged in)

Win32_DiskDrive
Query this class for its ".PNPDeviceID" and its ".Size".
The PnpDeviceID for my Kingston DataTraveler stick returns as...

"USBSTOR\DISK&VEN_KINGSTON&PROD_DATATRAVELER_2.0&REV_PMAP\5B8805000088&0"
The Size for this drive returns as "8003197440"

...which gives you lots of info to work with from just 2 values.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[pcbpradeep]

;1608158']Look into working with WMI using the "\root\CIMV2" namespace
The info
you want is available but requires running queries in several WMI
classes so that you're sure you're polling the correct USB device...

Win32_LogicalDisk
Query this class filtering only USB drives via
DriveType = "1" (RemoveableDisk).
Make sure you check its ID matches its drive letter (Path).
Note that the device.ID is its system descriptor (drive assigned
when it got plugged in)

Win32_DiskDrive
Query this class for its ".PNPDeviceID" and its ".Size".
The PnpDeviceID for my Kingston DataTraveler stick returns as...

"USBSTOR\DISK&VEN_KINGSTON&PROD_DATATRAVELER_2.0&REV_PMAP\5B8805000088&0"
The Size for this drive returns as "8003197440"

...which gives you lots of info to work with from just 2 values.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hello Garry,

thank you for the help, I am sorry to say that i am not a software eng
and i thought that as we can print time and system os details in cell o
excel we can print the above also. Ok any way i will not give up. I hav
googled and found below cods, but according the author it can'
differentiate the usb flash drive which has been plugged in and i
outputs all usb port details including some thing like usb mouse, coul
you please help me in any way?

and one more thing even i don't know how to use it in excel, i a
expecting your precious time and help.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Management;
using System.IO;

namespace MyWMITry
{
static class Program
{
static void Main()
{
//writing to text file d:\\wordpressmod1.txt
TextWriter tw = new StreamWriter("d:\\MyWMITry.txt");

ManagementScope scope = new ManagementScope("root\\CIMV2");
scope.Options.EnablePrivileges = true;
string Win32_USBControlerDevice = "Select * Fro
Win32_USBControllerDevice";
ObjectQuery query = ne
ObjectQuery(Win32_USBControlerDevice);
ManagementObjectSearcher searcher = ne
ManagementObjectSearcher(scope, query);
foreach (ManagementObject mgmtObj in searcher.Get())
{
string strDeviceName = mgmtObj["Dependent"].ToString();
string strQuotes = "'";
strDeviceName = strDeviceName.Replace("\"", strQuotes);
string[] arrDeviceName = strDeviceName.Split('=');
strDeviceName = arrDeviceName[1];
string Win32_PnPEntity = "Select * From Win32_PnPEntit
"
+ "Where DeviceID =" + strDeviceName;
ManagementObjectSearcher mySearcher =
new ManagementObjectSearcher(Win32_PnPEntity);
foreach (ManagementObject mobj in mySearcher.Get())
{
string strDeviceID = mobj["DeviceID"].ToString();
string[] arrDeviceID = strDeviceID.Split('\\');
tw.WriteLine("Device Description : "
+ mobj["Description"].ToString());
if (mobj["Manufacturer"] != null)
{
tw.WriteLine("Device Manufacturer : "
+ mobj["Manufacturer"].ToString());
}
tw.WriteLine("Device Version ID & Vendor ID : "
arrDeviceID[1]);
tw.WriteLine("Device ID : "
arrDeviceID[2].Trim('{', '}'));
tw.WriteLine();
}
}
// close the stream
tw.Close();
}
}
}


again thanking you and looking for your precious time and help

pradee

 

[GS]

First thing I note is that your code sample is not VBA. It's vb.net and
so won't work with VBA without some serious rewriting, which IMHO isn't
worth doing because it's the wrong approach.

As for your needs as I understand them, you want to be able to identify
if the drive selected by your user validates as a bonafide flash drive
(or a drive volume that has a "USB" interface). Why I suggest using WMI
is because this is most easily possible with it. I can also provide you
a pure VB[A] solution but that approach requires refs to several APIs,
and includes adding a class wrapper to your project. This is way more
trouble over using WMI!

To use WMI you only need to ref its library under Tools>>References...
in the VBA project's IDE. The process is not straight forward by any
means, but it's reliable IMO. I use this approach to lock app licenses
to a removable drive so my apps can be portable, thus usable on any PC.

To start with, you need to solicit the target USB drive via the
FolderPicker dialog.

Then you need to verify that the selected path is indeed a USB drive
via WMI's Win32_LogicalDiskToPartiton class to match the drive letter
and get its hardware index (position) on the OS. This is possible by
parsing the values in the 'Antecedent' and 'Dependent' properties. I
return this as a delimited string in the format "IndexriveLetter",
loaded into an array containing all info for all drives.

Next I query the Win32_DiskDrive class, filtering my query to return
only those drives that have a USB 'InterfaceType'. I do this to return
the info I need that matches the Index of the DriveLetter for the
target USB drive. This info is also loaded into a delimited string once
a match for Index is found. In my case, I collect the
"ModelnpDeviceID:Size" info. The PnpDeviceID contains compound info
and so will need to be parsed if you want to break it down into
individual bits of data.

I don't have generic code to do this so I'll have to piece snippets
together so you can have a single reusable function that you can work
with. You'll need to be patient with me as I have to find the
time/energy for this. (I have Lou Gehrig's)

Note that my approach will only work with drives, not all USB devices.
Since this is what my understanding of your need is, I think it'll be a
perfect solution...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I decided to use late binding so you won't need to set a ref to the WMI
library. Otherwise, just run the code. At the end is a Sub to test a
drive for being a USB volume. My goal was to make this as simple as
possible (which I think I accomplished that), and so now I'll use this
code to replace my proprietary stuff.<g>

HTH

Paste the following code in a standard module...

Option Explicit

'32-bit API declarations required for folder selection
Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias
"SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As
Long
Declare Function SHBrowseForFolder Lib "shell32.dll" Alias
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

'Type declarations
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type


Function Get_UsbDriveInfo$()
Dim sPath$, sDrvLetter$, sDrvIndex$, oWMI, vDrv, vDrives, i%, iPos%
'Solicit user for the path to the target drive
sPath = GetDirectory: If sPath = "" Then Exit Function
'We only want the drive letter
sPath = Left$(sPath, 1)

On Error GoTo ErrExit
Set oWMI =
GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set vDrives = oWMI.ExecQuery("Select * from
Win32_LogicalDiskToPartition")

i = 0
For Each vDrv In vDrives
'First match the drive letter
sDrvLetter = vDrv.Dependent: iPos = InStr(1, sDrvLetter, "=")
sDrvLetter = Mid$(sDrvLetter, iPos + 2, 1)
If sDrvLetter = sPath Then '//get its Index
sDrvIndex = vDrv.Antecedent: iPos = InStr(1, sDrvIndex, "#")
sDrvIndex = Mid$(sDrvIndex, iPos + 1, InStr(1, sDrvIndex, ",") -
(iPos + 1))
Exit For
End If
Next 'vDrv

'Verify it's a USB drive
Set vDrives = oWMI.ExecQuery("Select * from Win32_DiskDrive where
InterfaceType = ""USB""")
For Each vDrv In vDrives
If vDrv.Index = sDrvIndex Then
Get_UsbDriveInfo = Join(Array(vDrv.Model, vDrv.PnpDeviceID,
vDrv.Size), ":")
Exit For
End If
Next 'vDrv


ErrExit:
Set oWMI = Nothing: Set vDrives = Nothing


End Function 'Get_UsbDriveInfo

Function GetDirectory$(Optional Msg$)
' Opens the browse dialog for picking a folder

Dim bInfo As BROWSEINFO
Dim sPath$, r&, x&, iPos%

'Root folder = Desktop
bInfo.pidlRoot = 0&

'Title the dialog
If Msg = "" Then Msg = "Select a folder."
bInfo.lpszTitle = Msg

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
x = SHBrowseForFolder(bInfo)

'Parse the result
sPath = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal sPath)
If r Then
iPos = InStr(sPath, Chr$(0))
GetDirectory = Left(sPath, iPos - 1)
Else
GetDirectory = ""
End If

End Function 'GetDirectory

Sub Test_Get_UsbDriveInfo()
Dim sMsg$
If Len(Get_UsbDriveInfo) Then sMsg = "Valid USB drive" Else sMsg =
"Not a valid USB drive"
MsgBox sMsg
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Since the drive info is what we're after.., I modified the test sub as
follows...

Sub Test_Get_UsbDriveInfo()
Dim sDrvInfo$
Const sMsg$ = "Not a Valid USB drive"
sDrvInfo = Get_UsbDriveInfo
If Len(sDrvInfo) Then MsgBox sDrvInfo Else MsgBox sMsg
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

In the Get_UsbDriveInfo function the variable *i%* was a Paste carry
over that I forgot to remove since it's not used. Also, you can delete
the line preceeding the first loop as this was used to populate an
array of values. In this case we're only concerned with the target USB
drive, not all of them.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion