读取CATPart文件时CATIA VBA InStr函数失效问题求解

我要做一个不依赖CATIA的Excel BOM生成工具，写了VBA子程序把CATPart当文本文件读，用InStr函数能提取自定义参数，但提取CATIA原生零件号时卡壳了——打开CATPart文本明明能看到零件号跟在"ASMPRODUCT"后面，但InStr（包括InStrRev）死活识别不到这个字符串。相关代码如下：

Dim fsDataFile As Object
Dim sText As Object
Dim sPath As String
Dim line As String

Set fsDataFile = CreateObject("Scripting.FileSystemObject")

line_n = 0
'The fully qualified path to your lookup file
sPath = "some CATPart file"

'Create a text stream from your FileSystemObject
Set sText = fsDataFile.OpenTextFile(sPath, 1, False)

'Loop through each line in the text stream
Done = False
Do While sText.AtEndOfStream <> True
    line_n = line_n + 1
    line = sText.ReadLine
    'Some times the file contains the word more than once, the 
    'boolean variable Done avoid this problem.
    If InStr(1, line, "ASMPRODUCT", 1) <> 0 And Done = False Then
        Debug.Print line_n & " | PN: " & Mid(line, InStr(1, line,"ASMPRODUCT") + 11, 16)
        Done = True
    End If
Loop

sText.Close

• 编码不匹配是核心问题：CATPart文件默认是UTF-16LE（Unicode）编码，而FileSystemObject的OpenTextFile默认按ANSI读取，导致读取后的字符串乱码，InStr自然找不到"ASMPRODUCT"。换用ADODB.Stream按正确编码读取：

Dim objStream As Object
Dim sText As String
Dim sPath As String
Dim lineArr() As String
Dim i As Integer
Dim Done As Boolean

Set objStream = CreateObject("ADODB.Stream")
sPath = "your CATPart file path"

With objStream
    .Charset = "UTF-16LE"
    .Open
    .LoadFromFile sPath
    sText = .ReadText
    .Close
End With

lineArr = Split(sText, vbCrLf)
Done = False
For i = LBound(lineArr) To UBound(lineArr)
    If InStr(1, lineArr(i), "ASMPRODUCT", vbTextCompare) <> 0 And Not Done Then
        Debug.Print (i + 1) & " | PN: " & Mid(lineArr(i), InStr(1, lineArr(i), "ASMPRODUCT") + 11, 16)
        Done = True
    End If
Next i

Set objStream = Nothing

• 清理隐藏控制字符：如果编码没问题还是找不到，可能是"ASMPRODUCT"前后有不可见的控制字符，可以先对读取的行做清理：

' 去掉空字符和非打印字符
line = Replace(line, Chr(0), "")
line = WorksheetFunction.Clean(line)

• 直接解析XML结构：CATPart本质是XML格式，用XML DOM解析更稳定，直接定位到对应节点提取零件号，避免文本读取的各种坑：

Dim xmlDoc As Object
Dim node As Object
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False
xmlDoc.Load("your CATPart file path")

' 定位到ASMPRODUCT对应的零件号节点，可根据实际XML结构调整XPath
Set node = xmlDoc.SelectSingleNode("//*[@Name='ASMPRODUCT']//Attribute[@Name='PartNumber']")
If Not node Is Nothing Then
    Debug.Print "PN: " & node.Text
End If

Set xmlDoc = Nothing

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