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.
GnuManAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

argmysterCommented:
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!

Argmyster...
0
vbkidCommented:
GnuMan,
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.

<vbkid>
0
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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

vbkidCommented:
GnuMan,
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


<vbkid>
0
vbkidCommented:
Hi,
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)

rst.MoveLast
rst.MoveFirst

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
  rst.MoveNext
Loop
dbs.Close
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.


<vbkid>
0
NoggyCommented:
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)

GetOpenFilename_Exit:
    Set xlApp = Nothing
    Exit Function

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

End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GnuManAuthor Commented:
Adjusted points to 75
0
GnuManAuthor Commented:
argmyster,
certainly an answer, but vbkid and noggy have outperformed you in solving my problem.

vbkid,
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.

Noggy,
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!
0
NoggyCommented:
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.
0
argmysterCommented:
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.
0
GnuManAuthor Commented:
Argmyster,
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.