If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sum limit and marking | Sum Limit and marking | Worksheet Functions | 1 | December 20th, 2005 01:01 PM |
Exceeding the 65536 Items in a ComboBox Limit | Declan | Using Forms | 3 | November 16th, 2005 12:07 AM |
Limit on number of recipients | Sue Mosher [MVP-Outlook] | General Discussion | 1 | August 23rd, 2005 02:41 PM |
Character limit on unbound textbox | Tilman Bergt via AccessMonster.com | Using Forms | 1 | January 31st, 2005 12:23 PM |
Limit on Scenario name length | kraester | General Discussion | 2 | August 10th, 2004 09:41 AM |