Excel Forms Into A Databases
Discussion
Morning all
I'm just about to finish my first of two nights shifts, and have spent best part of the last 11 hours messing and fiddling with excel.
I have no problem creating forms, to allow for user input, and check boxes to be checked, but what I have no clue how to do (but need to do) is somehow export all the data into an access database.
Ideally, i'd like a button on the original spreadsheet which would handle the whole process.
It's a bit of an odd request and to be honest, i'm not expecting any replies, but - thought i'd put it out there for the PH techies to ponder.
Cheers
Adam
I'm just about to finish my first of two nights shifts, and have spent best part of the last 11 hours messing and fiddling with excel.
I have no problem creating forms, to allow for user input, and check boxes to be checked, but what I have no clue how to do (but need to do) is somehow export all the data into an access database.
Ideally, i'd like a button on the original spreadsheet which would handle the whole process.
It's a bit of an odd request and to be honest, i'm not expecting any replies, but - thought i'd put it out there for the PH techies to ponder.
Cheers
Adam
The answer is Visual Basic For Applications (VBA).
Presumably your forms collect data from users onto a sheet in the Excel workbook. You can connect a VBA function to a button on a form. "All" you need then do is code up the VBA such that it sequentially navigates through the data you have collected on a sheet somewhere and inserts records into an Access table.
A simple matter for any competent "Microsofty" programmer.
The VBA could handle error situations quite well too - like an inability to connect to the Access database and so on.
If you aren't a programmer, however, this may present a challenge. Its not end-user stuff to do. Sadly I don't know if there are any end-user friendly methods of doing this as, since I am a programmer, programming is the easy way to do almost anything for me.
And yes. Why aren't you doing the forms in Access? Much easier. I take it the people who will finally fill in the data on the sheet won't have access to Access (hoho)?
Presumably your forms collect data from users onto a sheet in the Excel workbook. You can connect a VBA function to a button on a form. "All" you need then do is code up the VBA such that it sequentially navigates through the data you have collected on a sheet somewhere and inserts records into an Access table.
A simple matter for any competent "Microsofty" programmer.
The VBA could handle error situations quite well too - like an inability to connect to the Access database and so on.
If you aren't a programmer, however, this may present a challenge. Its not end-user stuff to do. Sadly I don't know if there are any end-user friendly methods of doing this as, since I am a programmer, programming is the easy way to do almost anything for me.
And yes. Why aren't you doing the forms in Access? Much easier. I take it the people who will finally fill in the data on the sheet won't have access to Access (hoho)?
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff