Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: access vba read first 40 lines in a text file and write into excel

    Hi,

    Someone kindly provided me a function which can read all lines in a text file and write all lines into an excel file and it works extremely well. However, sometimes, I only need to read and then write the first 40 lines into excel. I want to skip the rest of lines, because they are not needed. Would you please help? How to change the following function to read and write the first 40 lines only? Thank you very much!


    Public Function ProcessTextFile(ByVal TextFileName As String, ByVal
    delim1 As String, ByVal ExcelFileName As String)

    Dim appExcel As Excel.Application
    Dim intHandle As Integer
    Dim strLine As String
    Dim varElements As Variant
    Dim intCol As Integer
    Dim intRow As Integer

    Set appExcel = New Excel.Application
    With appExcel

    .Visible = False
    .Workbooks.Add
    intHandle = FreeFile
    Open TextFileName For Input As #intHandle
    Do Until EOF(intHandle)
    intRow = intRow + 1
    Line Input #intHandle, strLine

    varElements = Split(strLine, delim1)
    For intCol = 0 To UBound(varElements)
    .ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
    .Selection = varElements(intCol)
    Next intCol
    Loop
    Close #intHandle
    .ActiveWorkbook.SaveAs filename:=ExcelFileName
    .Quit
    End With
    Set appExcel = Nothing

    End Function

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Change:
    Code:
    Do Until EOF(intHandle)
        intRow = intRow + 1
        Line Input #intHandle, strLine
    To:
    Code:
    Do Until EOF(intHandle)
        intRow = intRow + 1
        If intRow > 40 Then Exit Do
        Line Input #intHandle, strLine
    Have a nice day!

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by Sinndho View Post
    Change:
    Code:
    Do Until EOF(intHandle)
        intRow = intRow + 1
        Line Input #intHandle, strLine
    To:
    Code:
    Do Until EOF(intHandle)
        intRow = intRow + 1
        If intRow > 40 Then Exit Do
        Line Input #intHandle, strLine
    Thank you so much! It works great!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •