Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Text (VBA/Excel/97)

    I'm trying to import a text file using the VBA code below. However, when I pull the data each cell contains the separated data and the data from the cell before it. I've included a same of the the text file.

    Sub ImportRange()
    Set ImpRng = ActiveWorkbook.Sheets("sheet1").Range("A1")
    Open ThisWorkbook.Path & "sample.txt" For Input As #1
    r = 0
    c = 0
    Do While Not EOF(1)
    Line Input #1, data
    For i = 1 To Len(data)
    char = Mid(data, i, 1)
    If char = "," Then
    ImpRng.Offset(r, c) = txt
    c = c + 1
    Text = ""

    Else
    'Skip quote characters
    If char <> Chr(34) Then _
    txt = txt & Mid(data, i, 1)

    'End of the line?
    If i = Len(data) Then
    ImpRng.Offset(r, c) = txt
    c = c + 1
    txt = ""
    End If
    End If
    Next i
    c = 0
    r = r + 1
    Loop
    Close #1
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text (VBA/Excel/97)

    Do yourself a favor and tick "Require variable declaration" in the Editor tab of Tools | Options... (in the Visual Basic Editor).

    This will require you to explicitly declare each variable you use in your code. This seems annoying at first, but it will prevent a simple error as in your code: you use a variable "txt" several times, but once you misspell it as "text". Since you don't require explicit declarations, this error is not caught. So "text" is set to "", but "txt" keeps its value.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text (VBA/Excel/97)

    Thanks. It's done.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text (VBA/Excel/97)

    I'm trying to import a text file that is one long row. Each data point is separated by a comma and each "row" is separated by a forward slash. The program above works fine for the text files that I'm importing which have rows that are already separated, but it doesn't work for the file mentioned above. Is there a way in which I can adjust the import program so that it reads the one row file the same as the others? Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text (VBA/Excel/97)

    Change the Do ... Loop part of the code to

    <code> Do While Not EOF(1)</code>
    <code> Line Input #1, data</code>
    <code> For i = 1 To Len(data)</code>
    <code> char = Mid(data, i, 1)</code>
    <code> 'Check for /</code>
    <code> If char = "/" Then</code>
    <code> imprng.Offset(r, c) = txt</code>
    <code> txt = ""</code>
    <code> c = 0</code>
    <code> r = r + 1</code>
    <code> 'Check for ,</code>
    <code> ElseIf char = "," Then</code>
    <code> imprng.Offset(r, c) = txt</code>
    <code> c = c + 1</code>
    <code> txt = ""</code>
    <code> Else</code>
    <code> 'check for quote</code>
    <code> If char <> Chr(34) Then</code>
    <code> txt = txt & Mid(data, i, 1)</code>
    <code> End If</code>
    <code> 'End of the line?</code>
    <code> If i = Len(data) Then</code>
    <code> imprng.Offset(r, c) = txt</code>
    <code> c = 0</code>
    <code> r = r + 1</code>
    <code> txt = ""</code>
    <code> End If</code>
    <code> End If</code>
    <code> Next i</code>
    <code> Loop</code>

    There is an extra check for the forward slash.

Posting Permissions

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