GnuMan
asked on
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.
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>
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>
ASKER
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 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?
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>
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>
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
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>
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
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Adjusted points to 75
ASKER
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 https://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!
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 https://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!
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.
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.
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.
ASKER
Argmyster,
Your advice is taken to heart, so I WILL post some points for you: https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10288397
Thanks to all!
Your advice is taken to heart, so I WILL post some points for you: https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10288397
Thanks to all!
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...