Masters of VBA - please help - USB GPS via Userform

jansjane · May 12, 2016

Hello masters of VBA,

After spent days search on google for this and failed to understand what exactly what I need to do. I finally decided to post here and ask for some serious help.

I have a USB GPS Data Logger (HOLUX M-215+) is currently connected to my COM8 port with baud=4800 parity=N data=8 stop=1.
What I want to do is that while it is connected to my PC, I want to one click in a userform and then the N&W information in GPS is recorded and send to some specific cell in workbook. The usefrom is working now except GPS part.

I understand there are few diffent versions of codes out there. But, as a beginer of VBA, I dont even know where to put these codes, and how to make VBA reading information via USB GPS. Can someone please walk me through? Your kindness and knowledge is greatly appreciated.

Below is the code I found. But I really don't know how to make it work. Please help.
http://www.mrexcel.com/forum/excel-questions/847836-still-need-help-com-port-code.html

Thank you very much.
Ken
__________________________________________________________________

Mod edit : thread moved to appropriate forum !

Hui · May 13, 2016

Can you please attach your workbook ?

jansjane · May 13, 2016

Hui said:
Can you please attach your workbook ?

Hello Hui, I very much appreciate your time to help me out. Please see the attached sheet. I am very new to VBA. But I would love to learn if you are willing to teach me. Again, thank you so much for your time.

jansjane · May 14, 2016

Hello Hui, as a follow up to provide you addtional information.
I use TWedge as a receiver to test the connection. The setting of TWedge is Serial: Port=COM8 | Baud=4800 | DataBits=8 | StopBits=1; 01 - Display Data in TWedge; No Hotkey, No Timer
Please see below the raw data I received. Thank you very much for help me out.

20:21:20.609 INFO 0x00000000 Device-1 Data collection stopped!
20:21:20.068 INFO 0x00000000 Device-1 Data: G,169.24,T,,M,0.00,N,0.00,K,A*35

20:21:19.959 INFO 0x00000000 Device-1 Data: 8225,N,06939.8269,W,0.00,169.24,140516,,,A*7F

$GPVT
20:21:19.851 INFO 0x00000000 Device-1 Data: 1.43,244.9,M,-35.8,M,,*6A

$GPRMC,002133.000,A,4421.
20:21:19.742 INFO 0x00000000 Device-1 Data: $GPGGA,002133.000,4421.8225,N,06939.8269,W,1,6,
20:21:19.041 INFO 0x00000000 Device-1 Data: 69.24,T,,M,0.00,N,0.00,K,A*35

20:21:18.931 INFO 0x00000000 Device-1 Data: 8225,N,06939.8269,W,0.00,169.24,140516,,,A*7E

$GPVTG,1
20:21:18.833 INFO 0x00000000 Device-1 Data: ,W,1,6,1.43,244.9,M,-35.8,M,,*6B

$GPRMC,002132.000,A,4421.
20:21:18.708 INFO 0x00000000 Device-1 Data: $GPGGA,002132.000,4421.8225,N,06939.8269
20:21:18.053 INFO 0x00000000 Device-1 Data: TG,169.24,T,,M,0.00,N,0.00,K,A*35

20:21:17.943 INFO 0x00000000 Device-1 Data: .8225,N,06939.8269,W,0.00,169.24,140516,,,A*7D

$GPV
20:21:17.819 INFO 0x00000000 Device-1 Data: W,1,6,1.43,244.9,M,-35.8,M,,*68

$GPRMC,002131.000,A,4421
20:21:17.709 INFO 0x00000000 Device-1 Data: $GPGGA,002131.000,4421.8225,N,06939.8269,
20:21:17.522 INFO 0x00000000 Device-1 Data: 6,,,A*7C

$GPVTG,169.24,T,,M,0.00,N,0.00,K,A*35

20:21:17.413 INFO 0x00000000 Device-1 Data: ============= DEMO VERSION =============
20:21:17.304 INFO 0x00000000 Device-1 Data: GSV,3,3,11,12,13,317,,01,07,052,,42,,,*7A

$GPRMC,00
20:21:17.169 INFO 0x00000000 Device-1 Data: 34,064,,24,22,287,20,02,18,234,13,22,16,043,*7B

$GP
20:21:17.070 INFO 0x00000000 Device-1 Data: 4,27,06,63,231,30,28,41,152,22*7A

$GPGSV,3,2,11,03,
20:21:16.961 INFO 0x00000000 Device-1 Data: ,,,,,1.71,1.43,0.93*0F

$GPGSV,3,1,11,19,69,320,30,17,69,05
20:21:16.851 INFO 0x00000000 Device-1 Data: ,244.9,M,-35.8,M,,*69

$GPGSA,A,3,17,19,28,06,02,24,,
20:21:16.742 INFO 0x00000000 Device-1 Data: THIS IS A DEMO VERSION - (C) TEC-IT DATENVERARBEITUNG GMBH
20:21:16.103 INFO 0x00000000 Device-1 Data: ,T,,M,0.00,N,0.00,K,A*35

20:21:15.993 INFO 0x00000000 Device-1 Data: 6939.8269,W,0.00,169.24,140516,,,A*74

$GPVTG,169.24
20:21:15.884 INFO 0x00000000 Device-1 Data: 5.8,M,,*61

$GPRMC,002129.000,A,4421.8225,N,0
20:21:15.525 INFO 0x00000000 Device-1 Data collection started!

jansjane · May 14, 2016

Hello Hui, I spent some time to reach the following conculsion: below the set of data is what I want to capture in my userform. They matches with the actual GPS coordinates of my current location.

20:21:19.742 INFO 0x00000000 Device-1 Data: $GPGGA,002133.000,4421.8225,N,06939.8269,W,1,6,

Thank you very much and have a good weekend.
Jane

Hui · May 14, 2016

So I would import the data into an Array
then search through the array looking for records starting with $GPGGA
then extract the data

jansjane · May 14, 2016

Hi Hui thank you f replying. The issue for me now is how to capture the data. I don’t have vba to receive data. And I wish I can have a button in usrrform to one click and record. Thanks

Hui · May 15, 2016

I would follow the technique shown at:
http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port.html

As I can't test it, you are best to try it first and then come back with problems when/if you encounter them

jansjane · May 16, 2016

Hi Hui, thanks for replying. I finally get the code working and output data into cell A1 of sheet "log". The code I used is from http://www.ozgrid.com/forum/showthread.php?t=168472. Your provided link helps a lot too.

I however encountered couple more problems and hope you would be able to offer some guidance:

1. the COM Port# changes every time when USB GPS plug into the computer. I will have to manually change the com port# in module1.
Is there anyway the code can read USB come port # itself?

2. The typical set of data I received from GPS shows following:

8L?檴菏毷r職殏b罕0.00娛獖b姫偑奻bbb"?1
$GPVTG,194.51,T,,M,0.00,N,0.00,K,D*30
$GPGGA,205328.000,4321.8167,N,07639.8330,W,2,7,1.24,248.1,M,-35.8,M,0000,0000*68
$GPRMC,205328.000,A,4421.8167,N,07939.8330,W,0.00,194.51,150516,,,D*7E
$GPVTG,194.51,T,,

The highlighted shows (Latitude) N& (Longitude)W information i want to interpolated into a readable format. I found some code to "output" Latitude and shows:

Code:

Sub getlatitude()
 
Dim dat(3) As String
Dim Latitude As String
 
 
Dim tmp As String
Dim pos As Integer
 
dat(3) = Sheets("log").Range("A1")
 
tmp = dat(3)
 
'find 1st comma
pos = InStr(tmp, ",")
'truncate up to 1st comma
tmp = Right(tmp, Len(tmp) - pos)
 
'find 2nd comma
pos = InStr(tmp, ",")
'truncate up to 2nd comma
tmp = Right(tmp, Len(tmp) - pos)
 
Dim sNum As String
Dim dNum As Double
 
'find 3rd comma
pos = InStr(tmp, ",")
'truncate from comma to end-of-string
'this is the GPS number
tmp = Left(tmp, pos - 1)
 
 
'get significant digits, ie: "47"
pos = InStr(tmp, ".")
sNum = Left(tmp, pos - 3)
dNum = CDbl(sNum)
 
 
Dim sMantissa As String
Dim dMantissa As Double
 
'get mantissa
pos = Len(tmp) - Len(sNum)
sMantissa = Right(tmp, pos)
dMantissa = CDbl(sMantissa) / 60
 
 
'***result***
Latitude = CStr(dNum + dMantissa)
Sheets("log").Range("F20").Value = Latitude & "N"
 
 
End Sub

here shows an example of rationale:
(Latitude) 4321.8167 ---->43+21.8167/60=43.363611666666 degrees
the similar fashion for Longtitude
(Longtitude) 10601.6986 ----> 106 + 1.6986/60 = 106.02831 degrees;

Here is my question: How can I alter the code above to include Longitude as final output? In the ideal world, I would like "43.363611666N, 106.02831W" as my result.

Thank you very much for whoever can guide me for this.

jansjane · May 16, 2016

The spreadsheet example I used attached here.

Hui · May 16, 2016

Can you post the file with the output in it please

jansjane · May 16, 2016

Hello Hui, please see the attached file. Thank you~

Hui · May 17, 2016

Try adding this in somewhere:

Code:

Sub Extract_Coords()

Dim myStr As Variant
Dim arr1() As String, arr2() As String
Dim x As Integer, y As Integer, i As Integer


myStr = [A1]
arr1 = Split(myStr, Chr(10)) 'Split String into Lines
For x = LBound(arr1, 1) To UBound(arr1, 1)
  arr2 = Split(arr1(x), ",") 'Split Lines into Segments
  For y = LBound(arr2, 1) To UBound(arr2, 1)
 
  If Trim(arr2(y)) = "$GPGGA" Then
  For i = 1 To UBound(arr2, 1)
  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Northing
  Debug.Print "Northing = "; arr2(i - 1)
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Westing
  Debug.Print "Easting = "; arr2(i - 1)
  End If
  Next i
  End If
 
  Next y
Next x

I have never seen an Easting referred to as a Westing "W" but maybe they are in other countries

jansjane · May 17, 2016

Hello Hui, thank you so much for your time to put this together.
You are right that it should be expressed in NE, but because E negative for where I am so, it is expressed either in a negative sign & Easting; or simply Westning. Thank you~

I tried two ways to use your code, either way returned the result I wanted. I am pasting here to show you, and hopefully you could point me out where is wrong:

1.

Code:

Sub getlatitude()
 
Dim dat(3) As String
Dim Latitude As String
 
 
Dim tmp As String
Dim pos As Integer
Dim myStr As Variant
Dim arr1() As String, arr2() As String
Dim x As Integer, y As Integer, i As Integer
 
myStr = [A1]
arr1 = Split(myStr, Chr(10)) 'Split String into Lines
For x = LBound(arr1, 1) To UBound(arr1, 1)
  arr2 = Split(arr1(x), ",") 'Split Lines into Segments
  For y = LBound(arr2, 1) To UBound(arr2, 1)
 
  If Trim(arr2(y)) = "$GPGGA" Then
  For i = 1 To UBound(arr2, 1)
  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Northing
  Debug.Print "Northing = "; arr2(i - 1)
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Westing
  Debug.Print "Easting = "; arr2(i - 1)
  End If
  Next i
  End If
 
  Next y
 
dat(3) = Sheets("log").Range("A1")
 
tmp = dat(3)
 
'find 1st comma
pos = InStr(tmp, ",")
'truncate up to 1st comma
tmp = Right(tmp, Len(tmp) - pos)
 
'find 2nd comma
pos = InStr(tmp, ",")
'truncate up to 2nd comma
tmp = Right(tmp, Len(tmp) - pos)
 
Dim sNum As String
Dim dNum As Double
 
'find 3rd comma
pos = InStr(tmp, ",")
'truncate from comma to end-of-string
'this is the GPS number
tmp = Left(tmp, pos - 1)
 
 
'get significant digits, ie: "47"
pos = InStr(tmp, ".")
sNum = Left(tmp, pos - 3)
dNum = CDbl(sNum)
 
 
Dim sMantissa As String
Dim dMantissa As Double
 
'get mantissa
pos = Len(tmp) - Len(sNum)
sMantissa = Right(tmp, pos)
dMantissa = CDbl(sMantissa) / 60
 
 
'***result***
Latitude = CStr(dNum + dMantissa)
Sheets("log").Range("F20").Value = Latitude & "N"
 
 
Next x
 
 
End Sub

This returned an error code "run-time error "5". Invalid procedure call or argment

2. just used your code alone in a module and run

Code:

Sub Extract_Coords()
 
Dim myStr As Variant
Dim arr1() As String, arr2() As String
Dim x As Integer, y As Integer, i As Integer
 
 
myStr = [A1]
arr1 = Split(myStr, Chr(10)) 'Split String into Lines
For x = LBound(arr1, 1) To UBound(arr1, 1)
  arr2 = Split(arr1(x), ",") 'Split Lines into Segments
  For y = LBound(arr2, 1) To UBound(arr2, 1)
 
  If Trim(arr2(y)) = "$GPGGA" Then
  For i = 1 To UBound(arr2, 1)
  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Northing
  Debug.Print "Northing = "; arr2(i - 1)
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Westing
  Debug.Print "Easting = "; arr2(i - 1)
  End If
  Next i
  End If
 
  Next y
Next x
 
End Sub

This returns:
Northing = 4421.8235
Easting = 07843.3333

It would be ideal to be
"44.363725, -78.722222" or "44.363725N, 78.722222W"

Thank you for your time. Let me know if I am asking too much as you have already helped me in a great way!

Hui · May 18, 2016

Copy both the below into a Code Module in VBA
You can run the Extract_Coords routine directly once you have imported the data
or call it from the Start module

Code:

Sub Extract_Coords()

Dim myStr As Variant
Dim arr1() As String, arr2() As String
Dim x As Integer, y As Integer, i As Integer


myStr = [A1]
arr1 = Split(myStr, Chr(10)) 'Split String into Lines
For x = LBound(arr1, 1) To UBound(arr1, 1)
  arr2 = Split(arr1(x), ",") 'Split Lines into Segments
  For y = LBound(arr2, 1) To UBound(arr2, 1)
  If Trim(arr2(y)) = "$GPGGA" Then
  For i = 1 To UBound(arr2, 1)
  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Latitude
  Range("B1") = "Northing = " + CStr(ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "S" Then
  'arr2(i - 1) is the Latitude
  Range("B1") = "Northing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Longitude
  Range("B2") = "Westing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "E" Then
  'arr2(i - 1) is the Longitude
  Range("B2") = "Easting = " + CStr(ConvToDeg(arr2(i - 1)))
  End If
  Next i
  End If
  Next y
Next x

End Sub

Code:

Private Function ConvToDeg(myDeg) As Double
  Dim Deg As Double
  Dim temp As Double
  Dim temp2 As Double

  Deg = Int(myDeg / 100)
  'Debug.Print Deg
  temp = Int(myDeg - 100 * Int(myDeg / 100)) / 60
  'Debug.Print temp
  temp2 = (myDeg - Int(myDeg)) / 60
  'Debug.Print temp2
  ConvToDeg = Deg + temp + temp2

End Function

see attached file:

jansjane · May 19, 2016

Thu so so much Hui. You are now making it even better. All I need is slight modification send them to another sheet. I m then done.. I cant thank you enough.

I remember you have a donation site somewhere, can you point it out for ? Thanks .

jansjane · May 19, 2016

I found it. And considering me bought you a coffee.

Hui · May 19, 2016

Simply change the lines:

Code:

  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Latitude  
Worksheets("New Sheet").Range("B1") = "Northing = " + CStr(ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "S" Then
  'arr2(i - 1) is the Latitude  
Worksheets("New Sheet").Range("B1") = "Northing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Longitude  
Worksheets("New Sheet").Range("B2") = "Westing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "E" Then
  'arr2(i - 1) is the Longitude  
Worksheets("New Sheet").Range("B2") = "Easting = " + CStr(ConvToDeg(arr2(i - 1)))
  End If

Note the new Worksheets("New Sheet"). addition above
Change the cell addresses as appropriate

jansjane · May 19, 2016

Hui said:

Simply change the lines:

Code:

  If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Latitude 
Worksheets("New Sheet").Range("B1") = "Northing = " + CStr(ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "S" Then
  'arr2(i - 1) is the Latitude 
Worksheets("New Sheet").Range("B1") = "Northing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Longitude 
Worksheets("New Sheet").Range("B2") = "Westing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "E" Then
  'arr2(i - 1) is the Longitude 
Worksheets("New Sheet").Range("B2") = "Easting = " + CStr(ConvToDeg(arr2(i - 1)))
  End If

Note the new Worksheets("New Sheet"). addition above
Change the cell addresses as appropriate

Thank you so very much Hui. I did not mean to take up your time again. I thought that I will to play around. Thank you so much!!

jansjane · May 19, 2016

Code:

If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Latitude 
Range("M65536").End(xlUp).Offset(1, 0).Select
Worksheets("New Sheet").Range("M1") = "Northing = " + CStr(ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "S" Then
  'arr2(i - 1) is the Latitude  Range("M65536").End(xlUp).Offset(1, 0).Select
Worksheets("New Sheet").Range("M1") = "Northing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Longitude 
Range("N65536").End(xlUp).Offset(1, 0).Select
Worksheets("New Sheet").Range("N2") = "Westing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "E" Then
  'arr2(i - 1) is the Longitude 
Range("N65536").End(xlUp).Offset(1, 0).Select
Worksheets("New Sheet").Range("N2") = "Easting = " + CStr(ConvToDeg(arr2(i - 1)))
  End If

Hello Hui,
I have been playing the code to hav Latitude to new worksheet, column M of next available cell; Longitude to new worksheet, column N of next available cell.

What I try to achieve is to capture lat an long each time open com.

However, It Is working right. Can you plz provide your insight? Where am am wrong? Thank you you lot.

Hui · May 19, 2016

Try:

Code:

If Trim(arr2(i)) = "N" Then
  'arr2(i - 1) is the Latitude
Worksheets("New Sheet").Range("M65536").End(xlUp).Offset(1, 0) = "Northing = " + CStr(ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "S" Then
  'arr2(i - 1) is the Latitude
Worksheets("New Sheet").Range("M65536").End(xlUp).Offset(1, 0) = "Northing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "W" Then
  'arr2(i - 1) is the Longitude
Worksheets("New Sheet").Range("N65536").End(xlUp).Offset(1, 0) = "Westing = " + CStr(-ConvToDeg(arr2(i - 1)))
  ElseIf Trim(arr2(i)) = "E" Then
  'arr2(i - 1) is the Longitude
Worksheets("New Sheet").Range("N65536").End(xlUp).Offset(1, 0) = "Easting = " + CStr(ConvToDeg(arr2(i - 1)))
  End If

Please attach the revised file for any follow up questions

jansjane · May 19, 2016

Hello Hui. Thank you for answering my question.

I put your code in, but shows an error code "Invalid Next Control Variable reference". I am attaching the sheet for your review.

Thank you!

jansjane · May 19, 2016

jansjane said:
Hello Hui. Thank you for answering my question.

I put your code in, but shows an error code "Invalid Next Control Variable reference". I am attaching the sheet for your review.

Thank you!

Hi Hui, I just want to update you. I manually entered the changes. It worked. But in a weird way. I have to be in "log" sheet, the code will return result. If I am in "new sheet" the cod will work but no result.
I don't have the GPS connected, wondering if that is the reason. I will update you next week for his. If you see something that is not right. Plz let me know. Thank you.

Hui · May 20, 2016

I have updated the code to work regardless of where you are
I have also simplified the Destination sheet to a single reference
Refer to the comments

jansjane · May 24, 2016

Hi Hui,
Thank you very much. It is working beautifully. I really appreciate your time and finally helped me to mak it work! You are awesome

Masters of VBA - please help - USB GPS via Userform

Member

Excel Ninja

Member

Attachments

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Member

Attachments

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Attachments

Member

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Attachments

Member

Attachments

Excel Ninja

Attachments

Member