Use Excel as data entry form

[Guest]

Please excuse the crosspost, but I'm using Internet Explorer to post this and
cannot figure out how to multi-post.

I have a need to collect some information from a wide variety of sources
throughout my internationally dispersed organization. I cannot use an
internet based questionnaire because of the sensitivity of the information
(can encrypt the Excel file), and don't want to hassle with the email
problems associated with distributing an Access database. However, I will
ultimately need to pull this data into an Access database.

My boss does not want to actually use a "spreadsheet" view within Excel to
enter the data, he wants to have Form like look and feel as the first page of
the spreadsheet. Anybody have any ideas for using Excel and Access in this
way?

 

[John Nurick]

Hi Dale,

If the information that's being collected is one record per workbook
so it can be imported into one table (even if normallised later) -
then it's simple.

Just set up the first sheet as an elegant, easy to fill in form - and
have a second sheet with a single row containing formulas that just
collect the data from the various cells in the form. Define that row
as a named range, protect the worksheets and workbook to stop users
mucking it up - and then when the time comes all you have to do is
unprotect the workbook with the password and import the named range.

If there are multiple records per workbook, or records to import into
more than one table, things are a bit more complicated, but variations
of the above are worth considering.

 

[Dale Fye]

Thanks, John.

I think I have figured it out. I created a form in Excel and am using the
workbooks Open event to open the form. I've got a pointer that will keep
track of the spreadsheet row (record) I am on, and will write updates to
that row of the spreadsheet as changes are made in the form. Not nearly as
elegant as Access, but it should fit the bill.

Dale