Help using macro

[Adrian Q via OfficeKB.com]

Hi ...
I need to use excel to open a certain text file that is over 65k lines....
How can i use macros to directly generate to a 2nd and 3rd spread sheet and
so on ...using macros?
can anybody give a little bit of help ?

Thanks guys
Cheers
Adrian

[David]

Excel is just over 65,000 lines, rows. Should work if it is less tham that.

--
David


"Adrian Q via OfficeKB.com" wrote:

>
> Hi ...
> I need to use excel to open a certain text file that is over 65k lines....
> How can i use macros to directly generate to a 2nd and 3rd spread sheet and
> so on ...using macros?
> can anybody give a little bit of help ?
>
> Thanks guys
> Cheers
> Adrian
>

[Norman Jones]

Hi Adrian,

Perhaps the following routine, posted by Berie Deitrick, would help:

'============================>>
Sub LargeFileImport()
' Bernie Deitrick
'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 = 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
'For xl95 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
'<<============================

---
Regards,
Norman

--

---
Regards,
Norman



"Adrian Q via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:51E0A6F2CB204@OfficeKB.com...
>
> Hi ...
> I need to use excel to open a certain text file that is over 65k lines....
> How can i use macros to directly generate to a 2nd and 3rd spread sheet
> and
> so on ...using macros?
> can anybody give a little bit of help ?
>
> Thanks guys
> Cheers
> Adrian