Jan 23 2007, 12:01 PM
I heard there is a way to make a sort've database in Excel with a user interface and a separate spreadsheet to capture the data. Does anyone know how to do this?
I know Excel is not intended to be a database but it's the best option I have that will possibly ensure some sort've data integrity and control over data entry. I will be importing all the data into a database for the reports, I just need a way to capture it and Excel is the only program that all the users have.
Any assistance would be appreciated.
Jan 23 2007, 12:09 PM
See the following:http://support.microsoft.com/kb/257819http://support.microsoft.com/kb/295646http://support.microsoft.com/kb/278973
If you're going to do this, you need to be very disciplined in how you structure your Excel files. They need to look like Access tables, really.
And do not open a Connection to an Open Workbook, this generates a Memory Leak.
Jan 23 2007, 12:23 PM
Thanks. This is kind've what I need but really wasn't looking at it being that complex.
I heard there is a way to make a user interface with combo boxes, list boxes, text boxes etc in Excel and that's what I need to do. I can see where those articles will come in hand though for the actual back-end. Thanks.
Jan 23 2007, 03:01 PM
Perhaps you mean a userform?
If you do just goto the VBE (Alt+F11) and Insert>Userform.
Jan 23 2007, 03:47 PM
A looong time ago we've had a discussion with Carole on how to simulate a form via a worksheet...
Perhaps it helps? Old Discussion
(scroll a bit down the thread)