VBA读取关闭Excel文件时无法获取全部表头及部分单元格值求助

问题根源 #

1. 原连接字符串里的HDR=NO会让OLEDB不识别第一行为表头，导致表头被当作普通数据处理，且CopyFromRecordset不会单独输出表头
2. OLEDB默认根据列前8行判断数据类型，当表头（文本）和下方数据（日期/数字）类型不匹配时，会丢失表头或部分General格式的数据

修正后的代码 #

Option Explicit

Sub GetDataFromClosedFile()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    
    Set cn = New ADODB.Connection
    
    ' 修改连接字符串：HDR=YES识别表头，IMEX=1强制文本读取避免类型冲突
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.Path & "/RawData.xlsm;" & _
        "Extended Properties='Excel 12.0 Macro;HDR=YES;IMEX=1';"    

    cn.Open
    
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [all_register$]"
    rs.Open
    
    ' 手动写入表头到第一行
    For i = 0 To rs.Fields.Count - 1
        Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' 从第二行开始写入数据
    Sheet1.Range("A2").CopyFromRecordset rs
    
    Sheet1.Range("A1").CurrentRegion.EntireColumn.AutoFit
    
    rs.Close
    cn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set cn = Nothing
End Sub

关键修改说明 #

• HDR=YES：告诉OLEDB把工作表第一行当作表头字段名，这样Recordset会正确识别表头信息
• IMEX=1：强制以文本模式读取所有列，解决表头（文本）和下方数据（日期/数字）类型不匹配导致的读取丢失问题，同时兼容General格式的混合数据
• 手动写入表头：CopyFromRecordset只会写入数据行，所以需要循环Recordset的字段名，把表头放到Sheet1的第一行
• 数据从第二行开始写入：避免覆盖刚写入的表头

内容的提问来源于stack exchange，提问作者Rizwan.A