Results 1 to 6 of 6

Thread: Help me to import more than 65000 records in Microsoft Excel

  1. #1
    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.

  2. #2
    Join Date
    Mar 2011
    Posts
    544

    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    804

    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

  4. #4
    Join Date
    Jun 2011
    Posts
    492

    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.

  5. #5
    Join Date
    Jun 2011
    Posts
    641

    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.

  6. #6
    Join Date
    Jun 2011
    Posts
    641

    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.

Similar Threads

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •