将包含一些空值的CSV文件读取到VBA数组中

Option Explicit

Sub ReadCSVFile()

    Dim FSO As Scripting.FileSystemObject
    Dim FileText As Scripting.TextStream
    Dim CSVData As Variant
    Dim LineArray As Variant
    Dim DataArray() As Variant
    Dim TempArray() As Variant
    Dim LineIndex As Long
    Dim ColumnIndex As Long
    
    ' 设置文件路径
    Dim FilePath As String
    FilePath = "C:\Path\To\Your\CSV\File.csv"
    
    ' 创建FileSystemObject对象
    Set FSO = New Scripting.FileSystemObject
    
    ' 打开CSV文件
    Set FileText = FSO.OpenTextFile(FilePath, ForReading)
    
    ' 读取CSV数据到临时数组
    LineIndex = 0
    Do While Not FileText.AtEndOfStream
        LineArray = Split(FileText.ReadLine, ",")
        
        ' 调整数组大小以容纳新的行
        If LineIndex > 0 Then
            ReDim Preserve TempArray(1 To UBound(DataArray, 1) + 1, 1 To UBound(DataArray, 2))
            For ColumnIndex = 1 To UBound(DataArray, 2)
                TempArray(UBound(TempArray, 1), ColumnIndex) = LineArray(ColumnIndex - 1)
            Next ColumnIndex
        Else
            ' 第一行作为列标题
            ReDim TempArray(1 To 1, 1 To UBound(LineArray) + 1)
            For ColumnIndex = 1 To UBound(LineArray) + 1
                TempArray(1, ColumnIndex) = LineArray(ColumnIndex - 1)
            Next ColumnIndex
        End If
        
        LineIndex = LineIndex + 1
    Loop
    
    ' 关闭文件
    FileText.Close
    
    ' 转换临时数组为二维数组
    ReDim DataArray(1 To UBound(TempArray, 1), 1 To UBound(TempArray, 2))
    For LineIndex = 1 To UBound(TempArray, 1)
        For ColumnIndex = 1 To UBound(TempArray, 2)
            DataArray(LineIndex, ColumnIndex) = TempArray(LineIndex, ColumnIndex)
        Next ColumnIndex
    Next LineIndex
    
    ' 现在，CSV数据已存储在DataArray数组中，可以进行后续处理
    
    ' 示例：输出数组中的数据到Immediate窗口
    For LineIndex = 1 To UBound(DataArray, 1)
        For ColumnIndex = 1 To UBound(DataArray, 2)
            Debug.Print DataArray(LineIndex, ColumnIndex)
        Next ColumnIndex
    Next LineIndex

End Sub