ExcelBanter - 64k row limit

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - 64k row limit (https://www.excelbanter.com/excel-worksheet-functions/86639-64k-row-limit.html)

I copied the macro and it works on text files, but will this work if you have
a .csv file or does it have to be text? What I have is an extra large .csv
file that needs to be broken up into a couple of sheets.

Thanks,

-- Jim

Here's a macro from Bernie Deitrick

--
Kind regards,

Niek Otten

Importing a large file into Excel. File MAY have more than 65,000 records

and will automatically start a new sheet.

Sub LargeFileImport()

'Bernie Deitrick's code for opening vary large text files in Excel

'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

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

"Jim" wrote in message ...
|I copied the macro and it works on text files, but will this work if you have
| a .csv file or does it have to be text? What I have is an extra large .csv
| file that needs to be broken up into a couple of sheets.
|
| Thanks,
|
| -- Jim

Thanks Niek, but that was about the same macro I had, and it works if the
file is about 1/2 the size, but if I use the full 37k file Excel just stops
responding? I went in with word and cut the file in about 1/2 the size and
it imports fine.

Does the "Input" function have a limit? When I use the full file it hangs
on the very first record when I step thru it in debug, but if the file is
smaller it brings in each record separately?? I'm sure confused.

Thanks for your help though.

"Niek Otten" wrote:

Here's a macro from Bernie Deitrick

--
Kind regards,

Niek Otten

Importing a large file into Excel. File MAY have more than 65,000 records

and will automatically start a new sheet.

Sub LargeFileImport()

'Bernie Deitrick's code for opening vary large text files in Excel

'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

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

"Jim" wrote in message ...
|I copied the macro and it works on text files, but will this work if you have
| a .csv file or does it have to be text? What I have is an extra large .csv
| file that needs to be broken up into a couple of sheets.
|
| Thanks,
|
| -- Jim

Seems it's not the macro, but an EOF character that is causing the hang-up.
If I open the file in WordPad and take the last " " char out of the file, and
save it them the macro works just fine. So I'll go down that path and see
what I can find. Thanks again for your help.

-- Jim

"Jim" wrote:

Thanks Niek, but that was about the same macro I had, and it works if the
file is about 1/2 the size, but if I use the full 37k file Excel just stops
responding? I went in with word and cut the file in about 1/2 the size and
it imports fine.

Does the "Input" function have a limit? When I use the full file it hangs
on the very first record when I step thru it in debug, but if the file is
smaller it brings in each record separately?? I'm sure confused.

Thanks for your help though.

"Niek Otten" wrote:

Here's a macro from Bernie Deitrick

--
Kind regards,

Niek Otten

Importing a large file into Excel. File MAY have more than 65,000 records

and will automatically start a new sheet.

Sub LargeFileImport()

'Bernie Deitrick's code for opening vary large text files in Excel

'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

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

"Jim" wrote in message ...
|I copied the macro and it works on text files, but will this work if you have
| a .csv file or does it have to be text? What I have is an extra large .csv
| file that needs to be broken up into a couple of sheets.
|
| Thanks,
|
| -- Jim