Solved CRUD in Excel

[skdn71]

Hi,

I hope this message finds you well.

I am currently working on a project that involves Data CRUD (Create, Read, Update, Delete) operations in Excel. I am reaching out to see if you have any experience or expertise in this area. Your insights and knowledge would be incredibly valuable to me.

If you have worked on similar projects or have any tips and best practices to share, I would greatly appreciate your assistance.

Thank you in advance for your time and help.

 

[Pat Hartman]

I am currently working on a project that involves Data CRUD (Create, Read, Update, Delete) operations in Excel. I am reaching out to see if you have any experience or expertise in this area. Your insights and knowledge would be incredibly valuable to me.

Excel is the wrong tool for the job. You need a database application. Access is an option you might want to look at.

 

[The_Doc_Man]

Unfortunately, each cell in an Excel spreadsheet is an independent entity. Anything you did, you would have to do to each cell. You can, if you wish, apply the same change to each cell, but cross-cell references might be affected negatively by having to do a bulk property assertion. When you declare a column's format, for example, Excel merely copies the format info for every cell in that column and leaves the format info in the column's header. But the main Excel GUI doesn't generally respond to row or column permissions and thus someone could probably override the cell's settings. True, you CAN protect an individual cell - but if you want role-based permissions that differ from one person to the next, I don't believe Excel has the ability to do fine-grained permissions. Access has that. Excel? I could be wrong... but I doubt it.

 

[Edgar_]

If you really want to use Excel as a CRUD application. You have roughly three options, IMO:
1. Using data models and connections, which is the majority of the Data tab
2. Using list objects
3. Not using list objects

With data models, you can import tables from external sources, make queries, merges, etc. It requires a steep learning curve, so you might want to learn a database system instead. These things require some setup and there aren't many people using these tools, so for many things you'll be on your own, but you'll find it has many advantages against the traditional approach. Not having to store the tables in the workbook is a very attractive way to work, for example; being able to create queries is another, because it's faster than looking up using built-in stuff.

For most people, using traditional methods is the way to go, so you'll find a lot of support for that. In the traditional way, CRUD can be done with or without list objects. There is a lot to say about this, but in general, list objects are easier to work with because ranges are easier to reference, and they can be referenced in many ways, even by column name. Without list objects, you would have to do some primitive operations like finding the last row to have the correct working range, it's more work and more prone to errors. Having the right ranges is key for CRUD, because you're either adding, deleting or referencing something in that range.

As for working with these ranges, you can choose to use forms or do data entry directly on the cells.

 

[plog]

Excel is the CRUD application. You're trying to reinvent a wheel with a wheel.

You can create records by typing in a blank row, you can read and even print data from it, you can update values by overwriting the cells, you can delete records by right clicking and deleting.

 

[The_Doc_Man]

Update: I may have interpreted your question incorrectly, thinking it was about user security and permissions. If that is NOT what you meant, please disregard my comment.

 

[Isaac]

Hi,

I hope this message finds you well.

I am currently working on a project that involves Data CRUD (Create, Read, Update, Delete) operations in Excel. I am reaching out to see if you have any experience or expertise in this area. Your insights and knowledge would be incredibly valuable to me.

If you have worked on similar projects or have any tips and best practices to share, I would greatly appreciate your assistance.

Thank you in advance for your time and help.

I've created automation and programs in Excel that involved Entering data, Reading data, and Deleting or Updating data but it was only because I didn't have Access or another better situated tool available to me - and it was pretty brutal. I'd suggest, as others have, taking a look at Microsoft Access which is a great tool for front end GUI and can also hold back end data, or you can use another system like Sql Server for the back end data.

 

[skdn71]

"Why do I need to press the reset button after I press page 2, 3, 4 to see the data? Can't I see the data directly when I press page 2? Please take a look."

 

[Isaac]

the .xlsm you have posted has a lot of 'interesting' code in it, members please be cautious with this file.
I'm not saying there's anything wrong with it, but please exercise caution.

Found a lot of worrisome stuff, like a registry edit

Private Sub SetWebBrowserMode()
' Force the WebBrowser control to use the latest IE version
Dim key As String
key = "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION\"
Shell "REG ADD " & key & "EXCEL.EXE /v " & ThisWorkbook.Name & " /t REG_DWORD /d 11001 /f", vbHide
End Sub