HomeSoftware


Excel VBA get GPS position from serial port
icdoo
Does anyone have a script that I could use in Excel vba to get my position from a serial GPS?
bobsonn
Has anyone shared a VBA script that would get the data from the GPS device (on a COM port) into an Excel spreadsheet cell?

Thanks,
Bob S
Ken in Regina
Hi Bob,

Welcome to the forum!

Exactly what data do you want to capture in the spreadsheet. A typical GPS receiver generates a ton of raw data.

Here is a list of all the "sentences" that can be produced by a typical NMEA compliant device.

http://aprs.gids.nl/nmea/

In the following link, scroll down to the sample file for an example of what can come out of an NMEA device in two seconds.

http://en.wikipedia.org/wiki/NMEA_0183
bobsonn
Thanks Ken. Glad to be here.

I enjoy running Time/Speed/Distance road rallies. The idea is to maintain an exact average speed. My thought was to grab the lat/long from the GPS device to figure the distance travel. Modern cars have digital odometers and there’s no “watching it roll” the tenths. In the blink of an eye, the tenth changes without warning. It would be nice to have the hundredths digit if you will. That would make for more accurate distances and thus more accurate maintaining of an exact average speed.

Three cars with sat nav systems that I have played with never have the sat nav tenths change with the odometer’s tenths digit. More than once the sat nav odometer changes while the car is not moving!

So my thought was to grab the lat/long, have the computer to the math, and make my own odometer. Making my own odometer allows for the car to move and the miles run up, run down (like when getting back on course), or not at all.

I have a USB GPS device that came with Microsoft’s Streets and Trips. It’s a u-blox device. I downloaded the u-center and have seen it spit out NMEA sentences. I am alright with the rate and volume of data I could be dealing with.

My problem seems to be getting the sentence into the spreadsheet or VBA program where I can use it.

Ideas?

Bob
Ken in Regina
I used to have an Excel spreadsheet that could capture the output from an air quality monitor through the serial port into the spreadsheet for analysis. The macro/script was written by someone else. But if I can find the spreadsheet I can shoot it to you and that might give you an idea how to do it.
Ken in Regina
Here's the spreadsheet I mentioned (attached). I don't know if it will help but give it a look. It works quite nicely to capture specific defined "sentences" from an air quality monitor (particle counts) coming in through the serial port.

I know it works through virtual serial ports because the two laptops I've used it with did not have actual serial ports. I use a USB-to-serial cable to connect and the driver for the cable just creates a virtual COM port.
Attached Files
File Type: zip DylosCharting.zip (55.3 KB)
bobsonn
Thanks Ken. You locked the VBA portion of the spreadsheet. If you'd like to repost the spreadsheet without the protection or email me directly (bob.sonntag@att.net), I'll give it a look-see.
Ken in Regina
Sorry, I never tried to look at the code so I didn't realize it was locked. The guy I got it from must have locked it. That was on a woodworking forum I haven't been on in years. And I don't recall who the author was. Wish I could be more helpful.
bobsonn
Guess that's why there are hacker forums too.
Ken in Regina
It's 6 or 7 years old. Any possible value to the originator is long since past. And you don't want to use it for its original purpose anyway. So knock yourself out.
Boyd
Just a thought.... I also have the S&T uBlox GPS and it's pretty bad - doesn't hold a lock very well and position tends to drift. We have discussed this here before. I wonder if it has the accuracy for what you want to do?

Have you considered smartphone apps? I'm sure there are many, but this came up with a quick Google search. https://itunes.apple.com/us/app/odometer+/id496096478?mt=8
bobsonn
Thanks Boyd. I'm not a smartphone person. There are also phone apps that show average speed. I have the rest of the T/S/D program running in Excel.

I got super accurate (off the deep end) with a reed switch on the drive shaft and some data acquisition equipment to accumulate the closings of the switch. That gave distances to about 2.5 feet. The reed switch wore out at about 8000 miles...well past it's expected life of 5,000,000 actions. So I can either climb under the car to disable the switch, or try something else. Plus, the GPS offers no hard-wired connection to the car.

But I appreciate the thought.

Bob
Ken in Regina
If you want precision similar to your reed switch setup you will be sorely disappointed with a GPS. Especially one like the MS device, as Boyd said.
Boyd
I think you'll find that that uBlox will report that it travels a pretty significant distance even when your car is stationary, due to position errors. Consumer GPS devices are only accurate within about 5 meters under good conditions. And the uBlox is likely much worse than that.

I did some testing with three different GPS devices - all of which are much better than the uBlox IMO - and posted the results in our thread about the Garmin GLO: http://www.laptopgpsworld.com/4860-garmin-glo-bluetooth-glonass-receiver-p3#post45453

These units were all sitting stationary with a clear sky view and look how much of a variation there was in position readings recorded every second over a half hour period....

laptopgpsworld.com About