Thread: Converting LARGE text files to excel
View Single Post
  #2  
Old 06-11-2009, 02:04 PM
mrice's Avatar
mrice mrice is offline
Forum Guru
  
Join Date: 22 Jun 2004
Location: Surrey, England
MS Office Version:Excel 2007
Posts: 1,851
mrice has been very helpful
Re: Converting LARGE text files to excel
This macro should do it, albeit slowly.

Code:
Sub Test()
Open "C:\temp\mytextfile.txt" For Input As #1
Do While Not EOF(1)
    Counter = Counter + 1
    Line Input #1, FileLine
    ActiveSheet.Cells(Counter, 1) = FileLine
    If Counter = 65000 Then
        Counter = 0
        Sheets.Add
    End If
Loop
Close #1
End Sub
Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

You will need to edit the path\filename.

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.
__________________
Martin

Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
Reply With Quote