Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Help me to import more than 65000 records in Microsoft Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 21-02-2012
Member
 
Join Date: Feb 2012
Posts: 13
Help me to import more than 65000 records in Microsoft Excel

I want to import 90,000 records into excel from an access table. I do not know the right way of doing this. I tried once to directly a access file inside excel which caused system to crash completely. I just do not want to spoil my data. Any help on the same is much appreaciated. Thanks.

Reply With Quote
  #2  
Old 21-02-2012
Member
 
Join Date: Mar 2011
Posts: 536
Re: Help me to import more than 65000 records in Microsoft Excel

You need to use XL 2007 for that. Previous versions are limited to 65000 rows. Importing a large file into Excel. File MAY have more than 65,000 records ' and will automatically start a new sheet.
Reply With Quote
  #3  
Old 21-02-2012
Member
 
Join Date: Jun 2011
Posts: 792
Re: Help me to import more than 65000 records in Microsoft Excel

Here is a small code that might help you out.
Code:
Sub LargeFileImport()
'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
Reply With Quote
  #4  
Old 21-02-2012
Member
 
Join Date: Jun 2011
Posts: 484
Re: Help me to import more than 65000 records in Microsoft Excel

Depending what you are trying to do you could hook an XL pivot table to the Access database. The pivot cache is not limited to a specific number of records. I have gone to a little over 1 million records that way. There is a memory limit issue but with 90k records you should be ok.
Reply With Quote
  #5  
Old 21-02-2012
Member
 
Join Date: Jun 2011
Posts: 627
Re: Help me to import more than 65000 records in Microsoft Excel

I downloaded the Excel 2007 trial version, yet the rows are still at 65k. Is this still a limitation on the trial version. If not, how do I get the expanded matrix to appear.
Reply With Quote
  #6  
Old 21-02-2012
Member
 
Join Date: Jun 2011
Posts: 627
Re: Help me to import more than 65000 records in Microsoft Excel

Could you please elaborate on how one would "hook an XL pivot table to Access". My client each month pulls down as many as a million records of sales data from an Excel pivot via OLE Database Query. They are currently cutting this data up into chunks < 65K and manually doing calcs that could be automated in Access. What I'd like to do is cut Excel out of the loop altogether if possible by virtue of the pivot hook to Access you mentioned.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Help me to import more than 65000 records in Microsoft Excel"
Thread Thread Starter Forum Replies Last Post
How to Import more than one XML files in Microsoft Excel Thedevotee MS Office Support 2 18-01-2012 05:57 PM
How to import a .xps file in Microsoft Excel 2007 The-Farmer MS Office Support 2 17-01-2012 12:24 PM
How to match records from two Sheets in Microsoft Excel Shaina Na MS Office Support 3 14-01-2012 05:13 PM
How to create .qif files in Microsoft Excel and Import to Quicken Dennis Racket MS Office Support 2 11-01-2012 11:46 AM
Import DNS records from another zone Zeno Windows Server Help 2 19-03-2008 02:43 AM


All times are GMT +5.5. The time now is 10:39 AM.