Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro modification for Text file (2003)

    I have the following macro that brings a csv file into Excel. I have a text file that I would like to use instead of the csv file. I used the text file but it doesn't put the fields in columns. Can this macro be modified to bring in a text file. I open the text file in Excel but it only brings in one page and stops. I am losing 5000 records. Thanks for your help.

    Option Explicit
    Sub ImportMultSheets()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "Cocuments and Settingslid7kseMy documentsRobert NeftSchoolsCopt500_OutofPocket.csv"
    lLimit = 65536
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    If lRow = lLimit + 1 Then
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    End If
    Loop
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    Close #1
    Set wks = Nothing
    End Sub

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

    Re: Macro modification for Text file (2003)

    A csv (comma separated values) file uses comma as field delimiter.
    How are the data stored in your text file?
    If the fields are separated by a delimiter different from a comma, for excample a tab, you must change the code accordingly: in bothTextToColumns commands, you should change Comma:=True to Tab:=True or SemiColon:=True or ...
    If the file has fixed width columns (padded with spaces), it becomes more complicated, you must change DataType:=xlDelimited to DataType:=xlFixedWidth and you must explicitly specify the position and data type of each column.

Posting Permissions

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