I want to import 90,000 records into excel from an access table

You need to use XL 2007 for that. Previous versions are limited to 65000 rows.
--
Brevity is the soul of wit.


"mmatz" wrote:

I want to import 90,000 records into excel from an access table

' ================================================== =========
' 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 very 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

' ================================================== =========


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mmatz" wrote in message ...
|I want to import 90,000 records into excel from an access table

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...
--
HTH...

Jim Thomlinson


"mmatz" wrote:

I want to import 90,000 records into excel from an access table

You'll have to use VBA code to split the records across multiple worksheets.
See http://www.cpearson.com/excel/ImportBigFiles.htm for example code to do
this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"mmatz" wrote in message
...
I want to import 90,000 records into excel from an access table

Dave F

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? Thanks

"Dave F" wrote:

You need to use XL 2007 for that. Previous versions are limited to 65000 rows.
--
Brevity is the soul of wit.


"mmatz" wrote:

I want to import 90,000 records into excel from an access table

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.


--
Bellisor


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"mmatz" wrote:

I want to import 90,000 records into excel from an access table