Excel: getting more than 65,000 rows

Is there an add in I can get to allow me to manipulate over 65,000 rows of data with excel?  I need to be able to play with 250,000 records or more.
Who is Participating?
NoggyConnect With a Mentor Commented:
Yeah, you're limited to 65536 rows, I'm afraid. However, with that quantity of records, i would seriously suggest MS Access. You can still access Excel functions that are not available in Access. To do this, you will need to put a wrapper around the Excel functions you want to use:
1. With a module open, goto Tools|References.
2. Select the Reference for Microsoft Excel 8.0.
3. Below is a sample wrapped Excel function:

Public Function GetOpenFilename(ByVal FileFilter As String, Optional ByVal Title As String, Optional ByVal ButtonText As String, Optional ByVal MultiSelect As Boolean) As Variant
    On Error GoTo GetOpenFilename_Error
    Dim xlApp As New Excel.Application
    GetOpenFilename = xlApp.GetOpenFilename(FileFilter, Title:=Title, ButtonText:=ButtonText, MultiSelect:=MultiSelect)

    Set xlApp = Nothing
    Exit Function

'   *****************
'   The following section is only used for debugging
        'Stop: Resume
'   *****************
    ShowError "zvbExcel", "GetOpenFilename"
    GetOpenFilename = "False"
    Resume GetOpenFilename_Exit

End Function
Hi GnuMan

Unfortunately, that is a limitation of Excel 97 or 2000.  No add-in can change that.  There is no way to overcome that shortfall.  You'll have to use another program application to overcome it.  Access is a good start.

Hope this clears things up!

Give us some more details. May be You can Consider putting the rows in the Second Sheet. It all depends how do you process the records/rows.

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

GnuManAuthor Commented:
Yuck!  I definitely need excel's power and versatility, AND it's the program I'm most used to.  I've got a statistical analysis add-in called Analyse-It on excel too.

I don't recall Access having much number crunching ability.  Can excel functions, etc. operate remotely on an access database?  Or does it need to import the data?
I did work and execute the Update operations on the SQL Server Database.If you can give an example of a small task I can write the code to do that and send it to you. My email Id is vbkid21@hotmail.com

Following is the code which updates the Database with being brought into the Spread Sheet. You can enter the Data in the Spread Sheet and upload into the Database.

Make sure you have the refereneces to MicroSoft DAO 2.5/3.5 Libraries
I am not sure whether you are comfortable with the SQL code etc.

I had put  a button on the Sheet and assigned the Following Macro to That.

I added a field Salary to the existing 'Employees' table.

Dim dbs As Database
Dim rst As Recordset
Dim strSql As String
Sub Access_Click()
Set dbs = OpenDatabase("C:\NorthWind.mdb")

strSql = "SELECT LastName, FirstName, " _
      & "EmployeeID ," & "Salary " _
        & "FROM employees; "

Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset)


Do While Not rst.EOF
  If rst.Fields("Salary") > 1500 Then
     strSql = "Update employees set salary = Salary + salary * .10 where EmployeeId = " & rst.Fields("EmployeeId")
     dbs.Execute strSql
     MsgBox "Greater Than 1500 and  was paid Bonus"
  End If
End Sub

Again this is only for making sure what we can do through Excel. We can do most of the thigs that a conventional Front End tool Can do. I do not have that number of records that you have so I could not test this code. This code as such runs fine.

GnuManAuthor Commented:
Adjusted points to 75
GnuManAuthor Commented:
certainly an answer, but vbkid and noggy have outperformed you in solving my problem.

you've given valuable info, but noggy wins.  go to http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10287996  for some points I've set aside for you.

Thanks a bunch.  your packaging should work.  I won't have awhile to try it, so I hope you don't mind if I come back with another question in this thread...

Thanks all!
GnuMan - No problem and thanks for the points and grade.

You will find that Access can actually do most of the number crunching that Excel can do. However, as I have indicated, there are some functions that Access can't use. But, in those circumstances, you can either use wrapper functions or use a subset of data and then apply it to an embedded Excel sheet or similar. Never tried this last one myself though as Access does most things I want anyway.
Understand and thanks for the points.

Yes, you are correct I did answer your question as it was origianly stated.  I am glad you have found a workaround (not a solution) to your guestion.  

I didn't spend much time with you on it, because I felt you had your answer, and you can't change the fact that Excel will only allow 65536 rows per sheet.

Hope you enjoy using Experts, I know I do.  

Those who help GnuMan find a workaround for the inability of Excel to handle more than 65536 rows per sheet deserve the points.

My recommendation to you is forget using Excel if you need to manage more than 65536 rows of data per sheet in this application.  You are only heading into a dark abyss that will swollow you whole, trust me!

Argmyster...again, thanks for the points.
GnuManAuthor Commented:
Your advice is taken to heart, so I WILL post some points for you:  http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10288397

Thanks to all!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.