Help me to import more than 65000 records in Microsoft Excel

[Shard.K]

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.

[Erakna]

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.

[DanielaA]

Here is a small code that might help you out.

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

[GALIENA]

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.

[QUENBY]

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.

[QUENBY]

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.