如何用VBS读取被Excel打开的UTF-8编码CSV文件内容？

核心问题分析 #

Excel打开文件时会施加独占式锁定，导致ADODB.Stream默认的独占打开模式失败；而FileSystemObject.OpenTextFile虽能绕过锁定，但仅支持UTF-16编码，无法直接处理UTF-8内容。以下是几种可行的解决方法：

方法1：修改ADODB.Stream的共享模式 #

通过指定Open方法的模式参数，允许共享读取，即可绕过Excel的独占锁定：

Set oTxtStream = CreateObject("ADODB.Stream")
oTxtStream.CharSet = "utf-8"
' adModeRead(3) + adModeShareDenyNone(16)：以共享读模式打开
oTxtStream.Open , 3, 16
oTxtStream.LoadFromFile(sMdmTestMetadataResults)
content = oTxtStream.ReadText
oTxtStream.Close
Set oTxtStream = Nothing

说明：adModeShareDenyNone参数允许其他进程（如Excel）保持文件打开状态，同时我们可以读取内容。

方法2：通过Windows API直接读取并转码 #

如果ADODB的方法仍有问题，可调用Windows API以共享模式打开文件，读取二进制内容后再转成UTF-8字符串：

Option Explicit

Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" ( _
    ByVal lpFileName As String, _
    ByVal dwDesiredAccess As Long, _
    ByVal dwShareMode As Long, _
    lpSecurityAttributes As Long, _
    ByVal dwCreationDisposition As Long, _
    ByVal dwFlagsAndAttributes As Long, _
    ByVal hTemplateFile As Long _
) As Long

Private Declare Function ReadFile Lib "kernel32" ( _
    ByVal hFile As Long, _
    ByVal lpBuffer As String, _
    ByVal nNumberOfBytesToRead As Long, _
    lpNumberOfBytesRead As Long, _
    lpOverlapped As Long _
) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long _
) As Long

Private Declare Function GetFileSize Lib "kernel32" ( _
    ByVal hFile As Long, _
    lpFileSizeHigh As Long _
) As Long

Const GENERIC_READ = &H80000000
Const FILE_SHARE_READ = &H1
Const FILE_SHARE_WRITE = &H2
Const OPEN_EXISTING = 3

Function ReadLockedUTF8File(filePath As String) As String
    Dim hFile As Long, fileSize As Long
    Dim buffer As String, bytesRead As Long
    
    ' 以共享读写模式打开文件，允许Excel同时持有锁
    hFile = CreateFile(filePath, GENERIC_READ, FILE_SHARE_READ Or FILE_SHARE_WRITE, 0, OPEN_EXISTING, 0, 0)
    
    If hFile = -1 Then
        ReadLockedUTF8File = ""
        Exit Function
    End If
    
    ' 获取文件大小
    fileSize = GetFileSize(hFile, 0)
    If fileSize = 0 Then
        CloseHandle hFile
        ReadLockedUTF8File = ""
        Exit Function
    End If
    
    ' 读取二进制内容
    buffer = String(fileSize, Chr(0))
    ReadFile hFile, buffer, fileSize, bytesRead, 0
    CloseHandle hFile
    
    ' 将二进制流转成UTF-8字符串
    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    stream.Type = 1 ' 二进制模式
    stream.Open
    stream.Write buffer
    stream.Position = 0
    stream.Type = 2 ' 文本模式
    stream.CharSet = "utf-8"
    ReadLockedUTF8File = stream.ReadText
    stream.Close
    Set stream = Nothing
End Function

' 使用示例
Dim content As String
content = ReadLockedUTF8File("C:\LOCAL\Jonas\resources\xls\MdmTestMetadata.csv")

说明：API直接操作文件底层，绕过了VBS内置对象的锁定限制，再通过ADODB.Stream完成UTF-8转码，完美适配需求。

方法3：直接访问已打开的Excel工作簿（针对Excel格式文件） #

如果目标文件是Excel格式（.xls/.xlsx），可以直接连接到已运行的Excel实例读取内容，完全避开文件锁：

Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.Workbooks("MdmTestMetadata.xlsx") ' 仅需文件名，无需路径
Set xlSheet = xlBook.Sheets(1)
' 读取整张表内容（可按需调整范围）
content = xlSheet.UsedRange.Value

说明：该方法仅适用于Excel文件，无需读取磁盘文件，直接获取内存中的工作簿数据。

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