I have a form which is basically an Excel sheet with several fields on it for
completion by users, I want the users to e-mail me the form so I can open it
and validate it, i then want some automatic (possibly a macro button) of
getting the data from the form into another excel spreadsheet, this
spreadsheet already has data in it, so i want the new data to take up the
next available row at the click of a button.

Also once the data is in the spreadsheet I want it to remain constant and
not dissapear should the source form be deleted or moved.

Is any of this possible or am I talking out of my ar$e.

Any help would be great

Matt

Hi Matt,

It is all possible and I have done exactly what you are trying to do. I
have got one set up that works. The problem is that you are asking for a
complete off the shelf solution and I don't think anyone else is going to be
able to give you that amount of time. I can't. You need to set a
significant amount of time aside and work on it as a project. The good part
is you will really understand a lot about excel by the end.

What I can do is point you to the steps you need, and some more information.

(first) you need to understand how macros work in Excel. If you have never
used the macro recorder, now is the time to start.

Here are the steps:
1) design a simple form in excel and understand how to protect it so that
other users can only change certain cells. Format - cells - protection AND
Tools protect worksheet.

2) understand how you can put a button on the form and link it to a macro.
A first one to start with would be a CLEAR button that would reset your form.
Edit toolbars forms AND Tools macros. Later you will need to get a
button linked to a macros that will actually send your mail for you.

3) how to send email from Excel. read this
http://www.rondebruin.nl/sendmail.htm
you don't really need to worry about fixing the data in the spreadsheet,
because if you send it as an email attachment, you will always have the
attachment "fixed" as a copy in the email anyway

4) how to retrieve the attachments. read this
http://www.dicks-clicks.com/excel/olRetrieving.htm
you will be opening your email, and saving the attachments to some folder
somewhere

5) back in excel, now you need to cook something up that will open several
workbooks one by one, and append them to a master workbook at the bottom.

I would work on those bits one by one if I were you, and post lots of
smaller questions here. It can definitely be done and you will get plenty of
help with smaller questions.

--
Allllen


"Matt-Sheff,UK" wrote:

I have a form which is basically an Excel sheet with several fields on it for
completion by users, I want the users to e-mail me the form so I can open it
and validate it, i then want some automatic (possibly a macro button) of
getting the data from the form into another excel spreadsheet, this
spreadsheet already has data in it, so i want the new data to take up the
next available row at the click of a button.

Also once the data is in the spreadsheet I want it to remain constant and
not dissapear should the source form be deleted or moved.

Is any of this possible or am I talking out of my ar$e.

Any help would be great

Matt

Hi thanks Alllen, thats a great start, many thanks for your help

Matt

"Allllen" wrote:

Hi Matt,

It is all possible and I have done exactly what you are trying to do. I
have got one set up that works. The problem is that you are asking for a
complete off the shelf solution and I don't think anyone else is going to be
able to give you that amount of time. I can't. You need to set a
significant amount of time aside and work on it as a project. The good part
is you will really understand a lot about excel by the end.

What I can do is point you to the steps you need, and some more information.

(first) you need to understand how macros work in Excel. If you have never
used the macro recorder, now is the time to start.

Here are the steps:
1) design a simple form in excel and understand how to protect it so that
other users can only change certain cells. Format - cells - protection AND
Tools protect worksheet.

2) understand how you can put a button on the form and link it to a macro.
A first one to start with would be a CLEAR button that would reset your form.
Edit toolbars forms AND Tools macros. Later you will need to get a
button linked to a macros that will actually send your mail for you.

3) how to send email from Excel. read this
http://www.rondebruin.nl/sendmail.htm
you don't really need to worry about fixing the data in the spreadsheet,
because if you send it as an email attachment, you will always have the
attachment "fixed" as a copy in the email anyway

4) how to retrieve the attachments. read this
http://www.dicks-clicks.com/excel/olRetrieving.htm
you will be opening your email, and saving the attachments to some folder
somewhere

5) back in excel, now you need to cook something up that will open several
workbooks one by one, and append them to a master workbook at the bottom.

I would work on those bits one by one if I were you, and post lots of
smaller questions here. It can definitely be done and you will get plenty of
help with smaller questions.

--
Allllen


"Matt-Sheff,UK" wrote:

I have a form which is basically an Excel sheet with several fields on it for
completion by users, I want the users to e-mail me the form so I can open it
and validate it, i then want some automatic (possibly a macro button) of
getting the data from the form into another excel spreadsheet, this
spreadsheet already has data in it, so i want the new data to take up the
next available row at the click of a button.

Also once the data is in the spreadsheet I want it to remain constant and
not dissapear should the source form be deleted or moved.

Is any of this possible or am I talking out of my ar$e.

Any help would be great

Matt