文本文件导入时如何区分连续分隔符与11空格占位符？

问题背景 #

需要优化现有VBA宏，使其兼容两种文本数据格式：

• 格式1：使用连续空格（最多8个）作为字段分隔符
• 格式2：使用单个空格分隔字段，但缺失的列用恰好11个空格占位

当前宏中设置TextFileConsecutiveDelimiter = True，会将11个连续空格合并为单个分隔符，导致列偏移，最终在汇总数据时触发「运行时错误13：类型不匹配」。

现有VBA代码 #

Private Sub Cmdpopulate_Click()
filei = 0
filepath = InputBox("Please enter file path to be imported") & ""                                                                                        'asks user for the file path (the files should be named with integers sequentially)
filemax = InputBox("How many files do you wish to import?")                                                                                                                 'asks user how many files to import, this sets a maximum number to cycle through
Do While filei < filemax                                                                                                                                                    'begins the file import loop, starting at filei (initially 0) up to filemax (defined above)
filei = filei + 1
filename = filei & ".txt"                                                                                                                                               'filename is the current filei integer and the extention
foffset = filei + 19
imptxt                                                                                                                                                                  'import file sub routine (see below)
Loop
add_frames
format_tables
Sheet1.Cells(1, 1).Select
'    cmdpopulate.Visible = False
End Sub

Public Sub imptxt()
Sheet2.Range("a4").CurrentRegion.Offset(500, 0).Resize(, 40).Clear                                                                                                          'clears the table
With Sheet2.QueryTables.Add(Connection:= _
"TEXT;" & filepath & filename, Destination:=Sheet2.Range("$A$4"))
.Name = Sheet2.Range("b1").Value
.TextFilePlatform = 874
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "?"
.TextFileSpaceDelimiter = True
.TextFileConsecutiveDelimiter = True
.Refresh BackgroundQuery:=True
.RefreshStyle = xlOverwriteCells
End With                                                                                                                                                                    'opens file  filename (defined above) at filepath (defined above), delimites for '?' overwrites any data in existing cells
Sheet2.Range("a1") = filepath                                                                                                                                               'inserts filepath in cell a1, troubleshooting only
Sheet2.Range("a2") = filename                                                                                                                                               'inserts filename in cell b2, troubleshooting only
'  Sheet2.Select
If filei = 1 Then
headers
End If
send                                                                                                                                                                        'goes to the send subroutine to put data from the import table into the summary table
End Sub

数据样例 #

1. 单空格分隔数据集 #

Feature      Unit   Nominal    Actual      Tolerances      Deviation

Step 20 - 17 
Width         mm +017.00000 +016.91924 +00.20000 -00.20000 -000.08076       

Step 21 - 18 - Width 
Width         mm +014.00000 +014.00860 +00.20000 -00.20000 +000.00860       

Step 22 - 18 - Width 
Width         mm +014.00000 +013.98360 +00.20000 -00.20000 -000.01640         

Step 23 - 18 - Width
Width         mm +014.00000 +014.03760 +00.20000 -00.20000 +000.03760   

2. 连续空格（6-8个）分隔数据集 #

Feature    Unit    Nominal   Actual          Tolerances       Deviation 

Step 11 - 6.1   4.0 (+/- 0.4)
Radius      mm    +4.000    +4.111        +0.400      -0.400     +0.111          

Step 15 - 8   12 (+/- 0.4)
Radius      mm    +12.000   +12.407       +0.400      -0.400     +0.407           

Step 16 - 6.2  4 (+/- 0.4)
Radius      mm    +4.000    +3.890        +0.400      -0.400     -0.110               

Step 17 - 2 - 16.5 CtQ (+/- 0.5)
Max Width   mm    +16.500   +16.608       +0.500      -0.500     +0.108              

Step 19 - 6.3 - 4.0 (+/- 0.4)
Radius      mm    +4.112     +4.046      +0.400      -0.400     -0.066 

3. 含11空格占位的数据集（原宏报错样本） #

Feature      Unit  Nominal   Actual      Tolerances      Deviation   

Step 19 - Hole 11 - Dia
Diameter      in +0000.1630 +0000.1633 +000.0020 -000.0020 +0000.0003        

Step 20 - Hole 12 - Dia
Diameter      in +0000.1630 +0000.1634 +000.0020 -000.0020 +0000.0004   

Step 22 - Hole 1 - TP
True Positio  in            +0000.0010 +000.0100            +0000.0010    

Step 23 - Hole 2 - TP
True Positio  in             +0000.0027 +000.0100            +0000.0027     

Step 24 - Hole 3 - TP
X Location    in -0002.0460 -0002.0455                                  -0000.0005           
Y Location    in +0000.0000 -0000.0016                                  -0000.0016           
True Positio  in             +0000.0033 +000.0100            +0000.0033        

注：样例中空白位置为11个连续空格

解决方案 #

核心思路是先预处理文本内容：将11个连续空格替换为特殊分隔符，再让QueryTable同时识别空格（合并连续空格）和特殊分隔符，从而保留空白单元格的位置。

修改后的imptxt子过程如下：

Public Sub imptxt()
    Dim fileContent As String
    Dim tempPath As String
    
    ' 清空目标区域
    Sheet2.Range("a4").CurrentRegion.Offset(500, 0).Resize(, 40).Clear
    
    ' 读取原文本文件内容
    Open filepath & filename For Input As #1
    fileContent = Input$(LOF(1), 1)
    Close #1
    
    ' 将11个连续空格替换为特殊分隔符|
    fileContent = Replace(fileContent, String(11, " "), "|")
    
    ' 创建临时文件保存处理后的内容
    tempPath = Environ("TEMP") & "\temp_" & filename
    Open tempPath For Output As #1
    Print #1, fileContent
    Close #1
    
    ' 导入临时文件，设置双重分隔符
    With Sheet2.QueryTables.Add(Connection:= _
        "TEXT;" & tempPath, Destination:=Sheet2.Range("$A$4"))
        .Name = Sheet2.Range("b1").Value
        .TextFilePlatform = 874
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileSpaceDelimiter = True ' 启用空格分隔
        .TextFileConsecutiveDelimiter = True ' 合并普通连续空格为单个分隔符
        .TextFileOtherDelimiter = "|" ' 识别特殊分隔符为列分隔（对应原11空格的空白单元格）
        .Refresh BackgroundQuery:=True
        .RefreshStyle = xlOverwriteCells
    End With
    
    ' 删除临时文件
    Kill tempPath
    
    ' 写入调试信息
    Sheet2.Range("a1") = filepath
    Sheet2.Range("a2") = filename
    
    ' 首次导入时生成表头
    If filei = 1 Then
        headers
    End If
    
    ' 执行数据汇总
    send
End Sub

方案说明 #

1. 预处理文本：把11个连续空格替换为|，这样原空白占位的位置会被标记为独立分隔符
2. 临时文件导入：将处理后的内容写入临时文件，避免修改原文件
3. 双重分隔符设置：同时启用空格分隔和自定义|分隔符，TextFileConsecutiveDelimiter = True会合并普通连续空格，而|会保留原空白单元格的列位置，避免偏移

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