[已解决]请帮忙看下这个代码如何修改下让它只读取前9行数据呢？

qinshuai8507

Sub 读取数据()
    fileToOpen = Application.GetOpenFilename("文本(*.txt), *.txt", , "读取数据")
    If fileToOpen = False Then
        Exit Sub
    End If
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileToOpen, Destination:=Range("A1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

最佳答案

月排行榜 / 总排行榜

爱疯

2017-6-29 17:58

Sub test()
    Dim A, x, y

    '1）获取路径
    x = Application.GetOpenFilename("文本(*.txt), *.txt", , "读取数据")
    If x = False Then Exit Sub

    '2）读文本
    Open x For Input As #1
    A = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
    Close #1

    '3）导入工作簿
    A = Application.Transpose(A)
    x = 10: y = 19
    For i = x To y
        A(i - x + 1, 1) = A(i, 1)
    Next i
    Cells.Clear
    [a1].Resize(y - x + 1) = A

End Sub

跳转到最佳答案楼层

爱疯

Sub test()
    Dim x, A
   
    '1）获取路径
    x = Application.GetOpenFilename("文本(*.txt), *.txt", , "读取数据")
    If x = False Then Exit Sub
   
    '2）读文本
    Open x For Input As #1
    A = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
    Close #1
   
    '3）导入工作簿
    [a1].Resize(9) = Application.Transpose(A)
   
End Sub

qinshuai8507

爱疯 发表于 2017-6-29 10:08
Sub test()
    Dim x, A
   

非常好完美实现，谢谢大侠，请问如何再修改一下可以读取10-19行呢？

爱疯

Sub test()
    Dim A, x, y

    '1）获取路径
    x = Application.GetOpenFilename("文本(*.txt), *.txt", , "读取数据")
    If x = False Then Exit Sub

    '2）读文本
    Open x For Input As #1
    A = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
    Close #1

    '3）导入工作簿
    A = Application.Transpose(A)
    x = 10: y = 19
    For i = x To y
        A(i - x + 1, 1) = A(i, 1)
    Next i
    Cells.Clear
    [a1].Resize(y - x + 1) = A

End Sub