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?
--
Email address is not valid.
Please reply to newsgroup only.

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.

On Wed, 15 Aug 2007 09:06:03 -0700, Dale Fye <(E-Mail Removed)>
wrote:

>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 - Access MVP

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

"John Nurick" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> On Wed, 15 Aug 2007 09:06:03 -0700, Dale Fye <(E-Mail Removed)>
> wrote:
>
>>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 - Access MVP