Plz Help Masters of VBA - Capture Northing and Westing from USB GPS Data Logger

[hehanhan]

Dear 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.

Thank you very much.
Ken

 

[hehanhan]

anyone?

 

[John_w]

See if http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port-3.html#post4338508 gets you started.

 

[hehanhan]

See if http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port-3.html#post4338508 gets you started.

Hello dear John,
Thank you for your reply. In fact, your post was few first posts I read. Unfortunately, I was mot able to make it work. When I ran the code, everything grey out, for about 30 seconds and then nothing happens.
What idid was paste this to a module, then pressed f5, then clicked run macro.

Please take a look if this is correct. John, could you also advise what should have happened if the code is running? Again, I connected my USB GPS to com8, what I wish is to have the coordinates. I tested with TWedge, the port is feeding info.

Much appreciate your knowledge and kindness.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Receive_COM5()
    
    Dim COM5file As Integer
    Dim timeout As Date
    Dim record As String * 11, emptyRecord As String * 11
    Dim recLen As Integer
    Dim inputByte As Integer
    
    recLen = Len(record)
        
    'Open COM5 port with baud rate 2400, No parity, 8 data bits and 1 stop bit
    
    COM5file = FreeFile
    Open "COM8:4800,N,8,1" For Random As #COM5file Len = recLen
    
    'Monitor port for 30 seconds
    
    timeout = Now + TimeValue("00:00:30")
    
    Debug.Print "Started"
    
    While Now < timeout
        Get #COM5file, , record
        
        If record <> emptyRecord Then
        
            Debug.Print Now; "<" & record & ">"
            
            'Display each byte
            
            For i = 1 To recLen
                inputByte = Asc(Mid(record, i, 1))
                If inputByte = 0 Then
                    'No character in this position
                ElseIf inputByte >= 32 And inputByte <= 126 Then
                    'Printable character
                    Debug.Print "<" & inputByte & "> "; Chr(inputByte)
                Else
                    'Non-printable character
                    Debug.Print "<" & inputByte & ">"
                End If
            Next
        End If
        
        DoEvents
        Sleep 200
    Wend
    
    Close #COM5file
    Debug.Print "Finished"

End Sub

 

[John_w]

Yes, you've changed the code correctly, however it might not work for your data because the code expects fixed-length records of 11 bytes. Try the code at http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port-3.html#post4338508 instead. You'll have to debug the code to see what bytes, if any the code is receiving (shown in the Immediate Window in the VBA editor - press Ctrl+G to display it), and modify the code accordingly.

Cross-posted: Masters - Please help with COM port code... USB GPS via Userform

 

[hehanhan]

Yes, you've changed the code correctly, however it might not work for your data because the code expects fixed-length records of 11 bytes. Try the code at http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port-3.html#post4338508 instead. You'll have to debug the code to see what bytes, if any the code is receiving (shown in the Immediate Window in the VBA editor - press Ctrl+G to display it), and modify the code accordingly.

Cross-posted: Masters - Please help with COM port code... USB GPS via Userform

hi John,
Thank you for your time. I apologize for the cross post.
I loaded the code you suggested in the following. All it did was grey out and then saved to another excel sheet. When I opened the sheet, nothing is in there. I pasted the USB GPS specs below. Can you please let me know where I could find how many bytes I should put in the code? Much appreciate your time.

My computer is Windows7 Enterprise, 64bits, Core i5

I also took your suggestion to display the immediate message (Crt+G), it shows this:
5/13/2016 3:26:10 PM <0>
..
..
..
..
5/13/2016 3:26:23 PM <0>
5/13/2016 3:26:24 PM Finished

Public Sub Receive_COM5_and_Save2()
    
    Dim timeout As Date
    Dim byte1 As Byte, chars As String, lineEnding As String
    Dim COMport As Integer
    Dim destCell As Range, rowOffset As Long
    
    'Set starting cell where received data will be saved
    
    With ThisWorkbook.ActiveSheet
        .Cells.Clear
        Set destCell = .Range("A1")
        rowOffset = 0
    End With
    
    'Define line ending character(s)
    
    'lineEnding = vbCr
    lineEnding = vbCrLf
    
    'Monitor COM port for 30 seconds
    
    timeout = Now + TimeValue("00:00:30")
    
    COMport = FreeFile
    Close COMport
    
    'Open COM5 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit.  These are the default port settings
    'in HyperTerminal.  In HyperTerminal, use Flow control = None.
    
    Open "COM8:4800,N,8,1" For Random As #COMport Len = 1
    
    Debug.Print Now; "Started"
    
    chars = ""
    While Now < timeout
    
        'Get 1 byte
        Get #COMport, , byte1
        Debug.Print Now; IIf(Chr(byte1) < " ", "<" & byte1 & ">", Chr(byte1))
        
        chars = chars & Chr(byte1)
                        
        If Right(chars, Len(lineEnding)) = lineEnding Then
            Debug.Print "Line:" & Left(chars, Len(chars) - Len(lineEnding))
            destCell.Offset(rowOffset, 0).Value = Left(chars, Len(chars) - Len(lineEnding))
            rowOffset = rowOffset + 1
            chars = ""
        End If
                
        DoEvents
        
    Wend
    
    Close #COMport
    Debug.Print Now; "Finished"

    'Save sheet as an .xlsx file
    
    destCell.Parent.Copy
    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\COM_Port_Data.xlsx"
    ActiveWorkbook.Close savechanges:=False

End Sub

Specification


‧MTK MT3333 GPS/ GLONASS chipset

‧66 parallel searching, 22 tracking channels

‧Receiver : L1, 1575.42MHz

‧C/A Code : 1.023MHz

‧Update frequency: 1Hz

‧Sensitivity: -165dBm

‧Support SBAS ( WAAS, EGNOS, MSAS)

‧Baud rate : 4800

‧Datum : WGS84


Positioning


‧Without aid : 3.0M 2D-RMS

‧DGPS(WAAS, EGNOS, MSAS, RTCM) : 2.5M

‧Velocity : 0.1M/sec (Without aid)

‧Time : 0.1μs. Sync GPS time
*The above data is based on the specifications of the MTK GPS chip


Acquisition Time


‧Hot start : < 1 sec

‧Warm start : < 30 sec

‧Cold start : < 31 sec

‧Reacquisition : < 1 sec
*The above data is based on the specifications of the MTK GPS chip


Dynamic Conditions


‧Altitude Limit : ＜18000 meters
‧Velocity Limit : ＜515 M/sec
‧Acceleration Limit : ＜4G
‧Jerk Limit : 20 M/sec3


Host interface


‧USB 2.0
‧RS-232
‧RJ-45

 

[John_w]

Thank you for your time. I apologize for the cross post.

Check the forum rules for this and the other forum and it usually asks you to post links to any cross posts. Read Excelguru Help Site - A message to forum cross posters for the reasons.

5/13/2016 3:26:10 PM <0>
..
..
..
..
5/13/2016 3:26:23 PM <0>
5/13/2016 3:26:24 PM Finished

The <0>'s means the code is receiving byte values of zero.

There is nothing in the specs you posted which says what the data format is. I downloaded the manual and there is no data or record format specification, only a screenshot showing data lines being logged.

I suggest you run HyperTerminal (or similar) as the receiver, with the correct port settings. Once HyperTerminal displays the data being sent by the device, you will know the port settings are correct and be able to see the raw text stream, and modify the VBA code to handle this data.

 

[hehanhan]

Check the forum rules for this and the other forum and it usually asks you to post links to any cross posts. Read Excelguru Help Site - A message to forum cross posters for the reasons.

The <0>'s means the code is receiving byte values of zero.

There is nothing in the specs you posted which says what the data format is. I downloaded the manual and there is no data or record format specification, only a screenshot showing data lines being logged.

I suggest you run HyperTerminal (or similar) as the receiver, with the correct port settings. Once HyperTerminal displays the data being sent by the device, you will know the port settings are correct and be able to see the raw text stream, and modify the VBA code to handle this data.

Hello John, Understood. I tried to delete the dupicate post, but was not able to. First timer for this.

I took your suggestion to use TWedge as a receiver. The setting is Serial: Port=COM8 | Baud=4800 | DataBits=8 | StopBits=1; 01 - Display Data in TWedge; No Hotkey, No Timer
There are sections data I received from GPS. Please see below. Thank you very much for stick around and 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!

 

[hehanhan]

Hi John, I just want to quickly update you that the following 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.
Ken

 

[hehanhan]

Just want to quickly update to people who will need this.
I finally got the code working for me from this site: Serial Port Communication in Excel (VBA) | Electronics Open Source

I alter the code to output to a cell within the sheet and manipulate from there. However, I encountered with another problem. the COM Port# changes every time when USB GPS plug into the computer. I will have to manually change the com port#/ is there anyway the code can read USB come port #? Thanks. I know very few people know this.. I am just testing luck here