基于条件拼接表格行：Excel宏实现GPS转GPX格式问题

我把多辆汽车的GPS轨迹文件导入Excel，需要筛选指定车辆的数据并导出为GPX格式。原文件里有不少不需要的列，还要在保留的列之间添加指定文本。已经写了一个宏能筛选对应车辆，但它只会复制整行数据，没法生成我要的格式。

我知道用TEXTJOIN工作表函数能实现这个格式转换，但想用宏来完成却搞不定。下面是示例数据、现有宏代码和期望输出：

示例数据 #

Date/Time          Car# Junk Lat        Lon         Junk2       Converted Date/Time
20221125050122ES    6   0    27.19483   -82.43863   x           2022-11-25T05:01:22-05:00
20221125050158ES    6   0    27.20587   -82.44154   x           2022-11-25T05:01:58-05:00
20221125052215ES    1   0    27.35147   -82.47196   x           2022-11-25T05:22:15-05:00
20221125052355ES    2   0    27.14018   -82.41795   x           2022-11-25T05:23:55-05:00
20221125052449ES    2   0    27.15536   -82.42394   x           2022-11-25T05:24:49-05:00
20221125052519ES    1   0    27.35149   -82.47195   x           2022-11-25T05:25:19-05:00
20221125052539ES    2   0    27.16463   -82.431     x           2022-11-25T05:25:39-05:00
20221125054932ES    3   0    27.2988    -82.44879   x           2022-11-25T05:49:32-05:00
20221125055059ES    3   0    27.27847   -82.44901   x           2022-11-25T05:50:59-05:00
20221125055519ES    4   0    27.31564   -82.26689   x           2022-11-25T05:55:19-05:00
20221125060022ES    4   0    27.31564   -82.26692   x           2022-11-25T06:00:22-05:00
20221125060106ES    6   0    27.18927   -82.43754   x           2022-11-25T06:01:06-05:00
20221125062409ES    2   0    27.14827   -82.41893   x           2022-11-25T06:24:09-05:00
20221125064901ES    3   0    27.29893   -82.4458    x           2022-11-25T06:49:01-05:00
20221125065650ES    4   0    27.31566   -82.26689   x           2022-11-25T06:56:50-05:00
20221125065821ES    4   0    27.31564   -82.26691   x           2022-11-25T06:58:21-05:00
20221125072115ES    1   0    27.35146   -82.47197   x           2022-11-25T07:21:15-05:00

现有宏代码 #

Sub Getdata()
Dim DriverRange As Range
Worksheets(1).Select
Set DriverRange = Worksheets(1).Range("B1", Range("B" & Rows.Count).End(xlUp))
For Each cell In DriverRange
  If cell.Value = Worksheets(1).Range("C21") Then
    lr = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row
    cell.EntireRow.Copy Destination:=Worksheets(2).Range("A" & lr + 1)
  End If
Next cell
End Sub

期望输出（筛选车辆6时） #

trkpt lat="27.19483" lon="-82.43863" time/2022-11-25T05:01:22-05:00/
trkpt lat="27.20587" lon="-82.44154" time/2022-11-25T05:01:58-05:00/
trkpt lat="27.18927" lon="-82.43754" time/2022-11-25T06:01:06-05:00/

解决方法 #

不需要依赖TEXTJOIN函数，直接在宏里提取目标列数据并拼接成GPX格式即可，逻辑更直接可控。修改后的宏代码如下：

Sub GenerateGPXData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetRow As Long
    Dim carNumber As Variant
    
    ' 指定源工作表和目标工作表
    Set wsSource = ThisWorkbook.Worksheets(1)
    Set wsTarget = ThisWorkbook.Worksheets(2)
    
    ' 获取要筛选的车辆编号（来自C21单元格）
    carNumber = wsSource.Range("C21").Value
    
    ' 清空目标工作表原有数据
    wsTarget.Cells.Clear
    
    ' 获取源数据最后一行行号
    lastRow = wsSource.Range("B" & wsSource.Rows.Count).End(xlUp).Row
    
    ' 初始化目标工作表的起始行号
    targetRow = 1
    
    ' 遍历源数据（从第2行开始，跳过表头）
    For i = 2 To lastRow
        ' 判断当前行车辆编号是否匹配
        If wsSource.Cells(i, 2).Value = carNumber Then
            ' 提取需要的字段：Lat=第4列，Lon=第5列，转换后时间=第7列
            Dim latVal As String, lonVal As String, timeVal As String
            latVal = wsSource.Cells(i, 4).Value
            lonVal = wsSource.Cells(i, 5).Value
            timeVal = wsSource.Cells(i, 7).Value
            
            ' 拼接成指定的GPX格式字符串
            Dim gpxLine As String
            gpxLine = "trkpt lat=""" & latVal & """ lon=""" & lonVal & """ time/" & timeVal & "/"
            
            ' 将拼接好的内容写入目标工作表
            wsTarget.Cells(targetRow, 1).Value = gpxLine
            targetRow = targetRow + 1
        End If
    Next i
    
    MsgBox "GPX格式数据生成完成！", vbInformation
    
    ' 可选：直接导出为GPX文件
    ' Dim savePath As String
    ' savePath = Application.GetSaveAsFilename(FileFilter:="GPX Files (*.gpx), *.gpx")
    ' If savePath <> "False" Then
    '     wsTarget.SaveAs Filename:=savePath, FileFormat:=xlTextPrinter
    ' End If
End Sub

代码说明 #

• 移除了Select操作，直接指定工作表，运行更稳定高效
• 跳过表头行，只处理有效数据
• 匹配车辆编号后，精准提取所需列的值，直接拼接成目标格式
• 可选择开启末尾的导出逻辑，直接生成GPX文件，无需手动复制

内容的提问来源于stack exchange，提问作者Randyg1999