Problem Description:
Assume I have a spreadsheet with multiple rows (let's say close to 100) of class offerings and multiple column headings (i.e. course number, description, date, start time, location, etc.)
The first 1 or 2 rows would ideally have input cells for "Name" and "ID Number".
The first column should have some kind of selection mechanism (radio button - check box etc.)
So far, nothing difficult.... but here is where it gets tricky.
I want users to be able to fill in the 2 input fields, then make their "course selections" in the first column and hit an "email selections" button. Doing so should generate an email with a prefilled destination and subject. The body of the email should contain the contents of the cells in the rows that have been checked with the column headings. In other words, it should be a mini table of just the selected rows.
Ideas?
Today's Excel Challenge
-
Skinny Bastard
- shady character
- Posts: 4381
- Joined: Wed Oct 26, 2005 5:14 pm
-
raum
- Posts: 3944
- Joined: Sun Jul 24, 2005 10:51 am
QUOTE(Mr. SM @ Mar 5 2007, 01:38 PM) Problem Description:
Assume I have a spreadsheet with multiple rows (let's say close to 100) of class offerings and multiple column headings (i.e. course number, description, date, start time, location, etc.)
The first 1 or 2 rows would ideally have input cells for "Name" and "ID Number".
The first column should have some kind of selection mechanism (radio button - check box etc.)
So far, nothing difficult.... but here is where it gets tricky.
I want users to be able to fill in the 2 input fields, then make their "course selections" in the first column and hit an "email selections" button. Doing so should generate an email with a prefilled destination and subject. The body of the email should contain the contents of the cells in the rows that have been checked with the column headings. In other words, it should be a mini table of just the selected rows.
Ideas?
and why aren't you doing this in Access, again?
Seriously, this is a native function of Access as a relational database. Also, I don't understand why you would set it up as an email, instead of a shared workbook (even online) that is just updated at the point where you open and it reruns the macros that would query the ODBC. Hell, php would handle this better.
-----
Example would be a subscription list, with check boxes, and a send button.
In fact, making this as an automating document in word with (optionally including an email macro), and attaching it to emails would be much easier.
One of the reasons why is if you have 100 offerings to choose, that email subject line could get pretty incomprehensible with all the concatenation, and some classes might have the same destination, so they get multiple emails.
I would not recommend this in Excel as you laid it out. If you let me actually see file (my email is on here), I can see if there is a way to do it in Excel, but for now, I advise against it due to the fact that course lists are often subject to change. Also, a check box is a useless control on this spreadsheet... that is a database function to control events based on a boolean logic operator. It really doesn't belong in Excel, IMHO. Better to use a cell as the input, and use validation to assign integrity.
Assume I have a spreadsheet with multiple rows (let's say close to 100) of class offerings and multiple column headings (i.e. course number, description, date, start time, location, etc.)
The first 1 or 2 rows would ideally have input cells for "Name" and "ID Number".
The first column should have some kind of selection mechanism (radio button - check box etc.)
So far, nothing difficult.... but here is where it gets tricky.
I want users to be able to fill in the 2 input fields, then make their "course selections" in the first column and hit an "email selections" button. Doing so should generate an email with a prefilled destination and subject. The body of the email should contain the contents of the cells in the rows that have been checked with the column headings. In other words, it should be a mini table of just the selected rows.
Ideas?
and why aren't you doing this in Access, again?
Seriously, this is a native function of Access as a relational database. Also, I don't understand why you would set it up as an email, instead of a shared workbook (even online) that is just updated at the point where you open and it reruns the macros that would query the ODBC. Hell, php would handle this better.
-----
Example would be a subscription list, with check boxes, and a send button.
In fact, making this as an automating document in word with (optionally including an email macro), and attaching it to emails would be much easier.
One of the reasons why is if you have 100 offerings to choose, that email subject line could get pretty incomprehensible with all the concatenation, and some classes might have the same destination, so they get multiple emails.
I would not recommend this in Excel as you laid it out. If you let me actually see file (my email is on here), I can see if there is a way to do it in Excel, but for now, I advise against it due to the fact that course lists are often subject to change. Also, a check box is a useless control on this spreadsheet... that is a database function to control events based on a boolean logic operator. It really doesn't belong in Excel, IMHO. Better to use a cell as the input, and use validation to assign integrity.
| BBcode: | |
| Hide post links |
-
Skinny Bastard
- shady character
- Posts: 4381
- Joined: Wed Oct 26, 2005 5:14 pm
QUOTE(raum @ Mar 5 2007, 01:02 PM) and why aren't you doing this in Access, again?This will be a document linked to from an internal website were writing to a database isn't allowed. Not all of the user group (which is global) will have access installed, however they DO have excel. Otherwise, I would have done this in access... PHP is also NOT an option because of the same "intranet" policy limitations. Don't ask me to explain the policies, I didn't vote for them... LOL
-----
QUOTE(raum @ Mar 5 2007, 01:02 PM) In fact, making this as an automating document in word with (optionally including an email macro), and attaching it to emails would be much easier.
One of the reasons why is if you have 100 offerings to choose, that email subject line could get pretty incomprehensible with all the concatenation, and some classes might have the same destination, so they get multiple emails. The subject line and the destination email address will remain the same regardless of the other selctions made. The only thing that we want to change is the BODY text of the email. Can you explain / describe the word solution more fully to me?
-----
QUOTE(raum @ Mar 5 2007, 01:02 PM) In fact, making this as an automating document in word with (optionally including an email macro), and attaching it to emails would be much easier.
One of the reasons why is if you have 100 offerings to choose, that email subject line could get pretty incomprehensible with all the concatenation, and some classes might have the same destination, so they get multiple emails. The subject line and the destination email address will remain the same regardless of the other selctions made. The only thing that we want to change is the BODY text of the email. Can you explain / describe the word solution more fully to me?
| BBcode: | |
| Hide post links |
-
raum
- Posts: 3944
- Joined: Sun Jul 24, 2005 10:51 am
[quote name='Mr. SM' date='Mar 5 2007, 02:39 PM' post='170779']
This will be a document linked to from an internal website were writing to a database isn't allowed. Not all of the user group (which is global) will have access installed, however they DO have excel. Otherwise, I would have done this in access... PHP is also NOT an option because of the same "intranet" policy limitations. Don't ask me to explain the policies, I didn't vote for them... LOL [/quote]
Ok, you "unofficially" need a executable database to do this. You are playing with the kind of fire that IT Security nightmares are made of. If they have to get you some dollars to get a database client to build exe databases, don't be afraid to man up and let them know! Automation of file transfer by email is not something you want to try with embedded microsoft objects built by macros. That is how viral attacks get their way into your network.
Seriously, from what you say, this is about policies and Excel will never pass your security controls because of the macros you will need from the user end. If you can copy a part of a spreadsheet into an email and send it to an automated list of recipients, you need to be aware that you basically have a glaring security gap. The only other option I see is building a custom user form in Excel, and making sure your user base has VBA enabled in Excel, and writing to a source access file. Enabling macros to mail controlled sections of the file will require read/write access which can be exploited easily. This will be disatrous, unless your user base is capable of intermediate Excel usage or you recieve no outside communication; server wide. If all you need is a user base to set up elected rows (what limitiations exist, like pre-requisites or limit to how many can be chosen, or schedule conflicts, etc.). You either have a competent user base, or a damn difficult task...
how about each user has a network ID, and they send their copy of a worksheet (all named similarly, with the index key being their own network ID) to a sharepoint or network folder that can be used for an ODBC.
I know companies want to be able to automate everything, but a process like this in Excel would need an Administrator to ensure the task set flows.
-----
The subject line and the destination email address will remain the same regardless of the other selctions made. The only thing that we want to change is the BODY text of the email. Can you explain / describe the word solution more fully to me?
[/quote]
I am hazy on the details of how this would work for your case which is trying to copy a functionality best left to databases, but the VBA operator is HasRoutingSlip=True.
This will be a document linked to from an internal website were writing to a database isn't allowed. Not all of the user group (which is global) will have access installed, however they DO have excel. Otherwise, I would have done this in access... PHP is also NOT an option because of the same "intranet" policy limitations. Don't ask me to explain the policies, I didn't vote for them... LOL [/quote]
Ok, you "unofficially" need a executable database to do this. You are playing with the kind of fire that IT Security nightmares are made of. If they have to get you some dollars to get a database client to build exe databases, don't be afraid to man up and let them know! Automation of file transfer by email is not something you want to try with embedded microsoft objects built by macros. That is how viral attacks get their way into your network.
Seriously, from what you say, this is about policies and Excel will never pass your security controls because of the macros you will need from the user end. If you can copy a part of a spreadsheet into an email and send it to an automated list of recipients, you need to be aware that you basically have a glaring security gap. The only other option I see is building a custom user form in Excel, and making sure your user base has VBA enabled in Excel, and writing to a source access file. Enabling macros to mail controlled sections of the file will require read/write access which can be exploited easily. This will be disatrous, unless your user base is capable of intermediate Excel usage or you recieve no outside communication; server wide. If all you need is a user base to set up elected rows (what limitiations exist, like pre-requisites or limit to how many can be chosen, or schedule conflicts, etc.). You either have a competent user base, or a damn difficult task...
how about each user has a network ID, and they send their copy of a worksheet (all named similarly, with the index key being their own network ID) to a sharepoint or network folder that can be used for an ODBC.
I know companies want to be able to automate everything, but a process like this in Excel would need an Administrator to ensure the task set flows.
-----
The subject line and the destination email address will remain the same regardless of the other selctions made. The only thing that we want to change is the BODY text of the email. Can you explain / describe the word solution more fully to me?
[/quote]
I am hazy on the details of how this would work for your case which is trying to copy a functionality best left to databases, but the VBA operator is HasRoutingSlip=True.
| BBcode: | |
| Hide post links |