Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
64k row limit
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
64k row limit
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
64k row limit
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
64k row limit
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Character limit in cell | Excel Discussion (Misc queries) | |||
Nested If Limit | Excel Worksheet Functions | |||
Email Color and Limit | Excel Discussion (Misc queries) | |||
sum limit and marking | Excel Worksheet Functions | |||
Make the row limit of excel flexible -not fixed at 65536. | Excel Discussion (Misc queries) |