JohnnyBravo
10-20-2005, 10:05 AM
I'm sending out an RFI to 100 hospitals across the country. The RFI's relate to 14 different transplant types - here are a few examples:
-- Adult Heart
-- Adult Lung
-- Adult Liver
-- Pediatric Autologous
-- Pediatric Allogeneic
There's an excel workbook where we send to the hospitals and there's a tab for each of these transplant types. We'll call it Solids.xls. So before I send it, I have to delete the sheets that do not pertain to that hospital.
Example:
ABC Hospital addressed to Mr. Jones... blah blah blah..
At the end of the first paragraph, the transplant type is specified. All the information EXCEPT for the transplant type is stored in an Access db so merging the records hasn't been a problem. The inefficient part comes in where i have been (upto now) manually typing in the transplant type for each hospital.
New Idea: A friend suggested that I take my db of contacts and export it to Excel - which is a good idea. Then insert a separate column (name it as: "organ" or "transplant type" or whatever.) Now I can merge the hospital name, contact name, AND the TRANSPlANT type at the same type. Good deal. Here is where it gets more involved.
Remember that Excel workbook I mentioned earlier? Well the generic one contains sheets for every transplant type - all 14 of them. There are tabs w/ the names of the organ (or transplant type) so I simply open up the workbook, delete the one(s) that do not pertain to that hospital.
Question: In the worksheet (previously exported from the Access db), for the column heading of "transplant type", I would like to type numbers in place of organ. By assigning a number for each organ (transplant type), it saves time. In other words, If ABC Hospital specializes in Adult Heart, Adult Lung, Adult Liver, I want to type in 1, 3, 5.
How can I get VBA to (after I type in the number code) to find and replace the numerical assignment and for the actual organ. And do it for only the column specified.
How can I get VBA to pull up the "Solids.xls" file then delete the sheets that do that pertain to the hospital?
Everything has been done manually thus far - it is absolutely tedious and error prone - I don't like it one bit. There's has GOT to be a better way - no?
-- Adult Heart
-- Adult Lung
-- Adult Liver
-- Pediatric Autologous
-- Pediatric Allogeneic
There's an excel workbook where we send to the hospitals and there's a tab for each of these transplant types. We'll call it Solids.xls. So before I send it, I have to delete the sheets that do not pertain to that hospital.
Example:
ABC Hospital addressed to Mr. Jones... blah blah blah..
At the end of the first paragraph, the transplant type is specified. All the information EXCEPT for the transplant type is stored in an Access db so merging the records hasn't been a problem. The inefficient part comes in where i have been (upto now) manually typing in the transplant type for each hospital.
New Idea: A friend suggested that I take my db of contacts and export it to Excel - which is a good idea. Then insert a separate column (name it as: "organ" or "transplant type" or whatever.) Now I can merge the hospital name, contact name, AND the TRANSPlANT type at the same type. Good deal. Here is where it gets more involved.
Remember that Excel workbook I mentioned earlier? Well the generic one contains sheets for every transplant type - all 14 of them. There are tabs w/ the names of the organ (or transplant type) so I simply open up the workbook, delete the one(s) that do not pertain to that hospital.
Question: In the worksheet (previously exported from the Access db), for the column heading of "transplant type", I would like to type numbers in place of organ. By assigning a number for each organ (transplant type), it saves time. In other words, If ABC Hospital specializes in Adult Heart, Adult Lung, Adult Liver, I want to type in 1, 3, 5.
How can I get VBA to (after I type in the number code) to find and replace the numerical assignment and for the actual organ. And do it for only the column specified.
How can I get VBA to pull up the "Solids.xls" file then delete the sheets that do that pertain to the hospital?
Everything has been done manually thus far - it is absolutely tedious and error prone - I don't like it one bit. There's has GOT to be a better way - no?