Hi all,
I have a text file that contains more than 1 lakh lines.
the text file contains 2 fields, the field length of the 1st field is 22 chars.
I wanted to import that text file into excel spreadsheet.
Since the lines in text file is more than 1 lakh, I got help on this forum from RoyUK, to split the text file and write into 2 spreadsheet.
Code:
What current code does:Option Explicit Sub LargeFileImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = Application.GetOpenFilename '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 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
It reads lines and writes into excel spreadsheet in a active sheet and read line by line.
What I want:
I want some changes in the above code that will read the line as 2 fields and write the value of 1st field(22 chars) in column A and the 2nd field in Column B.
I want the writing/importing to be done from the first cell of the excelsheet and not from any active sheet.
Hoping for a solution.
Thanks in advance.
Bookmarks