Importing a larger file help

[TYEZ]

Hello, I want to use this file but with slight modifications. Can someone out there help me?
What I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest in D.
Is this possible?
Thanks so much!

This is the code I want to use:

Sub ImportLargeFile() 
'Imports text file into Excel workbook using ADO. 
'If the number of records exceeds 65536 then it splits it over more than one sheet. 

    Dim strFilePath As String, strFilename As String, strFullPath As String 
    Dim lngCounter As Long 
    Dim oConn As Object, oRS As Object, oFSObj As Object 

    'Get a text file name 
    strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") 

    If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog 

    'This gives us a full path name e.g. C:tempfolderfile.txt 
    'We need to split this into path and file name 
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 

    strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path 
    strFilename = oFSObj.GetFile(strFullPath).Name 


    'Open an ADO connection to the folder specified 
    Set oConn = CreateObject("ADODB.CONNECTION") 
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
               "Data Source=" & strFilePath & ";" & _ 
               "Extended Properties=""text;HDR=Yes;FMT=Delimited""" 

    Set oRS = CreateObject("ADODB.RECORDSET") 

    'Now actually open the text file and import into Excel 
    oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1 
    While Not oRS.EOF 
        Sheets.Add 
        ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536 
    Wend 

    oRS.Close 
    oConn.Close 

End Sub

Right now this is the code we are using, but the problem with this one is that you have to type int he files desintation and it splits it amoung 2 worksheets.

Sub LargeFileImport()

      'Dimension Variables
      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      FileName = InputBox("Please enter filename followed by.txt")
      'Check for no entry
      If FileName = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open FileName For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Create A New WorkBook With One Worksheet In It
      Workbooks.Add template:=xlWorksheet
      'Set The Counter to 1
      Counter = 1
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
          'Store Variable Data Into Active Cell
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
          
          'For Excel versions before Excel 97, change 65536 to 16384
          If ActiveCell.Row = 65536 Then
             'If On The Last Row Then Add A New Sheet
             ActiveWorkbook.Sheets.Add
          Else
             'If Not The Last Row Then Go One Cell Down
             ActiveCell.Offset(1, 0).Select
          End If
          'Increment the Counter By 1
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False

   

End Sub

Please help
Thanks

• Edited by Nate: Added code tags.

 

[NateO]

Writing, more so cell by cell, should beef up your processing time by quite a bit. Although I suspect the major overhead in my procedure is simply opening the Recordset and using GetRows.

Nulls are one of two reasons I used a custom Transpose function, Excel's Transpose function will bomb on them. The 2nd reason is that I just wanted to pull out pieces of the array...

Originally I wrote this for multi-field Recordsets... But now am assuming the OP only has one Field of Data in the Text file...