Excel VBA从ASCII文件导入数据并作图求平均值

Sub ImportDataAndCalculateAverage()
    Dim filePath As String
    Dim fileContent As String
    Dim dataArray() As String
    Dim sum As Double
    Dim count As Integer
    Dim average As Double
    Dim i As Integer
    
    ' 设置ASCII文件路径
    filePath = "C:\path\to\file.txt" ' 替换为实际的文件路径
    
    ' 打开文件并将内容读入变量
    Open filePath For Input As #1
    fileContent = Input$(LOF(1), 1)
    Close #1
    
    ' 将文件内容按行分割为数组
    dataArray = Split(fileContent, vbCrLf)
    
    ' 计算平均值
    count = 0
    For i = LBound(dataArray) To UBound(dataArray)
        If IsNumeric(dataArray(i)) Then
            sum = sum + CDbl(dataArray(i))
            count = count + 1
        End If
    Next i
    
    If count > 0 Then
        average = sum / count
        
        ' 在Excel中创建图表并绘制平均值
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets.Add
        ws.ChartObjects.Add(Left:=10, Width:=300, Top:=10, Height:=300).Chart.SetSourceData ws.Range("A1:B2")
        ws.Range("A1").Value = "Average"
        ws.Range("B1").Value = average
        
        ' 保存Excel文件
        ThisWorkbook.SaveAs "C:\path\to\output.xlsx" ' 替换为实际的输出文件路径
        
        ' 显示成功消息
        MsgBox "数据已成功导入并平均值已绘制。"
    Else
        MsgBox "无有效数据。"
    End If
End Sub