Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    Opening a CSV file > 65536 rows (2000)

    I have a number of CSV files that are more than 65536 records. I have been opening them in Access and using a long winded method of creating 2 excel files and then combining them into two sheets in the one workbook. Is there an easier way or some VBA that can do it? Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Opening a CSV file > 65536 rows (2000)

    Try this it reads a file stores it into an array then dumps the array into a sheet. When the sheet is filled it adds a new one. Modify as needed.


    <pre>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 = Application.GetOpenFilename( _
    filefilter:="Text Files (*.txt), *.txt")

    If sPathFileName = "False" Then
    MsgBox "Canceled"
    Exit Sub
    End If

    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
    Call ArrayToRange(sArray, wks.Name, "A1")
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    End If
    Set wks = Worksheets.Add
    Call ArrayToRange(sArray, wks.Name, "A1")
    Close #1
    Set wks = Nothing
    End Sub

    Sub ArrayToRange(vArray, sWks As String, sCell As String)
    'This routine puts the array into a worksheet range.
    'No check is made to ensure that the range is empty.
    'It will clear all the rows in the columns it will place
    'the data into.

    Dim lLCols As Long
    Dim lLRows As Long
    Dim lUCcols As Long
    Dim lURows As Long
    Dim iCol As Integer

    lLRows = LBound(vArray, 1)
    lURows = UBound(vArray, 1)
    lLCols = LBound(vArray, 2)
    lUCcols = UBound(vArray, 2)

    With Worksheets(sWks).Range(sCell)
    .Resize(65537 - .Row, lUCcols - lLCols + 1).ClearContents
    .Resize(lURows - lLRows + 1, lUCcols - lLCols + 1).Value = vArray
    End With
    End Sub</pre>

Posting Permissions

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