Excel VBA编译错误：Method or data member not found 问题求助

核心错误原因 #

编译错误“Method or data member not found”出现在Line = source.ReadLine，大概率是变量source的类型冲突：
如果代码中提前将source声明为了非TextStream类型的对象（比如Worksheet、Range），就会导致VBA无法识别ReadLine方法——因为只有FileSystemObject的TextStream对象才拥有这个方法。

具体修复步骤 #

1. 检查并修正变量声明
   在代码开头添加明确的变量声明，避免类型冲突：

   Dim source As Object
   Dim FSO As Object
   Dim target As Worksheet
   Dim LC1a_Gap() As String
   Dim Comp_Gaps() As String
   Dim Delimiter As String
   Dim Ind As Integer, ii As Integer, pp As Integer
   Dim Line As String, LineElements As Variant
   Dim Gaps As Integer
   

   同时删除任何错误声明source为其他类型的代码（比如Dim source As Worksheet）。

2. 修复未定义的Gaps变量
   原代码中For ii = 1 To Gaps的Gaps未赋值，替换为数组上限值避免硬编码：

   Gaps = UBound(LC1a_Gap)
   

   初始化数组时需先指定大小：

   ReDim LC1a_Gap(1 To 7)
   ReDim Comp_Gaps(1 To 7)
   

3. 优化代码避免不必要的Select操作
   原代码中Range("I" & Ind).Select依赖当前活动工作表，直接改为指定目标工作表赋值，更稳定高效：

   target.Range("I" & Ind).Value = LC1a_Gap(ii)
   

4. 修正循环逻辑（避免丢失第一行数据）
   原代码先执行Line = source.ReadLine跳过第一行，进入循环后又读一行，会导致第一行数据丢失。调整逻辑保留表头：

   ' 读取表头并写入
   Line = source.ReadLine
   LineElements = Split(Line, Delimiter)
   For pp = LBound(LineElements) To UBound(LineElements)
       target.Cells(Ind, pp + 1).Value = LineElements(pp)
   Next pp
   Ind = Ind + 1
   ' 读取剩余数据行
   Do While Not source.AtEndOfStream
       Line = source.ReadLine
       LineElements = Split(Line, Delimiter)
       For pp = LBound(LineElements) To UBound(LineElements)
           target.Cells(Ind, pp + 1).Value = LineElements(pp)
       Next pp
       Ind = Ind + 1
   Loop
   

完整修正后的代码示例 #

Sub ImportCSVs()
    ' Set up array for all result directories
    Dim LC1a_Gap() As String
    Dim Comp_Gaps() As String
    ReDim LC1a_Gap(1 To 7)
    ReDim Comp_Gaps(1 To 7)
    
    LC1a_Gap(1) = "D:\Atlas\LoadCase1a\Sample_to_Drum.csv"
    LC1a_Gap(2) = "D:\Atlas\LoadCase1a\Bot_Drum_to_Module.csv"
    LC1a_Gap(3) = "D:\Atlas\LoadCase1a\Module_to_Sleeve.csv"
    LC1a_Gap(4) = "D:\Atlas\LoadCase1a\Sleeve_to_TRIO.csv"
    LC1a_Gap(5) = "D:\Atlas\LoadCase1a\Bellow_to_Drum.csv"
    LC1a_Gap(6) = "D:\Atlas\LoadCase1a\Bellow_to_Module.csv"
    LC1a_Gap(7) = "D:\Atlas\LoadCase1a\Top_Drum_to_Module.csv"

    Comp_Gaps(1) = "Import Sample_to_Drum"
    Comp_Gaps(2) = "Import Drum_to_Module"
    Comp_Gaps(3) = "Import Module_to_Sleeve"
    Comp_Gaps(4) = "Import Sleeve_to_TRIO"
    Comp_Gaps(5) = "Import Bellow_to_Drum"
    Comp_Gaps(6) = "Import Bellow_to_Module"
    Comp_Gaps(7) = "Import TopDrum_to_Module"

    Dim Delimiter As String
    Dim Ind As Integer, ii As Integer, pp As Integer
    Dim Line As String, LineElements As Variant
    Dim Gaps As Integer
    Dim source As Object
    Dim FSO As Object
    Dim target As Worksheet
    
    Delimiter = ","
    Ind = 2
    Gaps = UBound(LC1a_Gap)

    For ii = 1 To Gaps
        Set target = ThisWorkbook.Sheets(Comp_Gaps(ii))
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set source = FSO.OpenTextFile(LC1a_Gap(ii))
        
        ' 直接写入文件路径，避免Select操作
        target.Range("I" & Ind).Value = LC1a_Gap(ii)
        
        ' 读取表头并写入
        Line = source.ReadLine
        LineElements = Split(Line, Delimiter)
        For pp = LBound(LineElements) To UBound(LineElements)
            target.Cells(Ind, pp + 1).Value = LineElements(pp)
        Next pp
        Ind = Ind + 1
        
        ' 读取剩余数据行
        Do While Not source.AtEndOfStream
            Line = source.ReadLine
            LineElements = Split(Line, Delimiter)
            For pp = LBound(LineElements) To UBound(LineElements)
                target.Cells(Ind, pp + 1).Value = LineElements(pp)
            Next pp
            Ind = Ind + 1
        Loop
        
        Set source = Nothing
        Set FSO = Nothing
        Ind = 2
    Next
End Sub

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