Author Topic: USB data packets into Excel  (Read 12305 times)

gr8guitar

  • Member
  • ***
  • Posts: 10
USB data packets into Excel
« on: June 20, 2019, 08:15:20 pm »
Hello. I was wondering if there are any VBA examples to import a typical USB HID data packet. I am using an HHDSoftware analyzer and can see the data but no way to export it to Excel. My observations of the analyzer is it seems USB is always "live" and has I hit a button or turn a dial, the data then is shown. This is not a keyboard, mouse or joystick however. The goal is to have Excel continuously monitor the USB device and update a cell since the device sends one packet at a time. Thank you.

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #2 on: June 21, 2019, 01:26:50 pm »
Hello. Thank you. I will look at that. I must admit, my VBA and VB skills need upgrading. I went to a website (http://www.matrixtsl.com/mmforums/viewtopic.php?p=52081&sid=67e3d8f6dd19a3c0112f11d677265953#p52081) and downloaded usbhid.zip. The code is for VB. I attempted to import it into Excel VBA but have many errors has the code did not translate directly. I also attempted to register but so far it did not take.In addition, it does access the USB HID controller but it only gives VendorID, ProductID, etc. I actually need the data packets.  Again, thank you for your time and information.

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research
Re: USB data packets into Excel
« Reply #3 on: June 21, 2019, 09:49:47 pm »
You could instead retrieve the data using VB .NET or C# .NET and write code that stores the data in Excel. I have example code for getting HID data here:

http://www.janaxelson.com/hidpage.htm#MyExampleCode

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #4 on: June 21, 2019, 10:14:18 pm »
Hello Thank you for the information. I did download that, from somewhere; I don't recall but I did not see that it could export the information. I would need to be able to access the data packet "live". The USB HID device is a flight controller and it puts out data when I turn a dial or press a button. From the picture I attached, I can see the data packet, in part. I also can see the data is in hexadecimal. I converted the hexadecimal to ASCII and the data is valid so the retrieval of the Generic HID Tester works great. I just didn't see how it can export the data "live."

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research
Re: USB data packets into Excel
« Reply #5 on: June 22, 2019, 10:21:00 am »
Yes, you would need to write code that places the data in an Excel file instead of displaying it on screen.

https://support.microsoft.com/en-us/help/247412/methods-for-transferring-data-to-excel-from-visual-basic

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #6 on: June 22, 2019, 01:22:46 pm »
Hello. Thank you for your reply. I had downloaded that before but I didn't see or know how to extract the USB HID data packets with that, especially from an *.exe file. I do have a couple of versions of VB that use user32.ll and mcHID.dll that are tapping into the USB HID. I'll just have to learn VB and see how I can convert to VBA. They're not as similar as I had hoped.  :). Honestly, I'd prefer to pay someone to do the Excel VBA part. I greatly appreciate your time and information.

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research
Re: USB data packets into Excel
« Reply #7 on: June 22, 2019, 06:31:27 pm »
Yes, you would need to modify the HID application's source code rather than working with the exe. Let us know if you have other specific questions relating to HID or USB.

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #8 on: June 22, 2019, 08:28:59 pm »
Hello. Thank you. Are we allowed to post part of the VBA code at this website? I have taken part of what I think I understand but still need help. I think my main issue is I don't understand fully the passing of values/variables... Again, the goal is to retrieve the USB data packets and put it into a cell on a continuous (or loop) basis. Using either an analyzer or the GenericHID.exe, I see the data when a button is pressed or a dial is rotated. Also, the analyzer and GenericHID, don't really respond unless I type in the VendorID (16c0) and the ProductID (486) or the corresponding decimal numbers. The VBA code so far seems to search for any USB HID and then give basic information, like above but not actual data packet that I can use...

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #9 on: June 23, 2019, 07:02:54 pm »
Hello. I think I'm getting there. When I typed in the known VendorID and ProductID, I get a response at:
   MyHandle = hidGetHandle(&H16C0, &H486)
To verify, I typed in the VendorID and ProductID of a different USBHID, I get different results:
MyHandle = hidGetHandle(&H1DD2, &H1150)

For example, the first one doesn't give me a serial number but the second one does (B76052). So now I know I'm connecting.  I was able to verify this with Device Manager. So I guess the remaining question is, which of the declared functions actually gives the data packets?

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research
Re: USB data packets into Excel
« Reply #10 on: June 23, 2019, 10:16:56 pm »
You can read HID Input reports using

ReadFile
Filestream.Read
HidD_GetInputReport

See my example code.

gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #11 on: June 24, 2019, 11:48:53 am »
Hello. Thank you for your reply. I hope you are referring to generichid_vb_46 or generic_vb_50. I did look at hid.vb but I don't see how this transfers to VBA format. For example, I don't recognize Fieldstream.Read format. I admit I'm a VBA newbie. I know nothing of VB or .net VB. It may be time to learn. Where may one obtain a interpreter/compiler for those?


gr8guitar

  • Member
  • ***
  • Posts: 10
Re: USB data packets into Excel
« Reply #13 on: June 24, 2019, 01:19:57 pm »
Hello. Thank you for the information. Do you know if the Community version is sufficient for this project? Also, how does:

ReadFile
Filestream.Read
HidD_GetInputReport

translate into VBA? Or what declared function is supplying the information?

Jan Axelson

  • Administrator
  • Frequent Contributor
  • *****
  • Posts: 3033
    • Lakeview Research
Re: USB data packets into Excel
« Reply #14 on: June 24, 2019, 10:57:33 pm »
Community version, yes.

I am not proficient with VBA.

https://stackoverflow.com/questions/11502788/is-excel-vba-the-same-as-vb-net