Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch File drag-n-drop processing.
Let's say I have a spreadsheet(questions.xls) that represents a
customer questionaire, formated in a fancy page layout. I will email that questionaire to 1000 people. They will fill it out and email it back to me. I have this other spreadsheet(answers.xls) that is just a table of data, representing the answers. I want to just drag the many returned questions files and drop one or more onto a batch file, or perhaps onto ahswers.xls itself, and have a macro copy over the data to the answers table and then leave the original in place. Any ideas? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch File drag-n-drop processing.
Someone may come up with a better idea than this, but it's probably the way
I'd deal with it. I'd set up a special folder just to do this work in. I'd put your answers.xls file into that folder and also put any returned surveys in there with it (that presents a file naming problem since they'll probably all be named questions.xls - but that's a separate issue). I'd also create a subfolder with a name like HasBeenProcessed or DoneWithThese Then I'd write code in answers.xls that would get the filenames of all other ..xls files in the same folder with it (checking file names against its own name so that it doesn't try to process itself) and open each one, one at a time, extract the data from it putting that into answers.xls, closing the questions#.xls file and then moving the questions#.xls file into that HasBeenProcessed subfolder. As I said earlier, you've got a bit of a filenaming problem with the questions.xls files going out and coming back. One way or another you've got some work to do to be able to dump them all into a single folder upon return. Possibly the easiest way to do this is to set up a macro that takes a single copy of that file and copies it out to disk giving unique name such as questions0001.xls, questions0002.xls, ... questions1000.xls and then attaching them individually to the outgoing emails. And that's a process I'd sure look into automating also <g "Beav" wrote: Let's say I have a spreadsheet(questions.xls) that represents a customer questionaire, formated in a fancy page layout. I will email that questionaire to 1000 people. They will fill it out and email it back to me. I have this other spreadsheet(answers.xls) that is just a table of data, representing the answers. I want to just drag the many returned questions files and drop one or more onto a batch file, or perhaps onto ahswers.xls itself, and have a macro copy over the data to the answers table and then leave the original in place. Any ideas? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch File drag-n-drop processing.
Hmmmm... a second method (still doesn't overcome the file naming problem)
would be to put a macro into the questions.xls file that you could run when it is returned to you that would dump the information into the answers.xls file. Then as you had them returned, you could open the attachment, run that macro and then just close the opened attachment without saving it. Not a batch process, per se, but is feasible. Just be sure that the macro exits gracefully if it cannot find the answers.xls file on a system - you may have some curious questionnaire recipients out there that will give it a go just to see what it will do. The problem with this option is that you may also 'scare' some of the folks who get the questionnaire if they get the big "This file contains Macros ... Enable, Disable,??" warning. I'd look more seriously at something like my earlier suggestion because of this. "Beav" wrote: Let's say I have a spreadsheet(questions.xls) that represents a customer questionaire, formated in a fancy page layout. I will email that questionaire to 1000 people. They will fill it out and email it back to me. I have this other spreadsheet(answers.xls) that is just a table of data, representing the answers. I want to just drag the many returned questions files and drop one or more onto a batch file, or perhaps onto ahswers.xls itself, and have a macro copy over the data to the answers table and then leave the original in place. Any ideas? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch File drag-n-drop processing.
Instead of a batch file, you should use vbScript. That will allow you to
access WScript's 'Arguments' collection that would be the file names including paths of any files that are dragged and dropped on the vbs file (or on a shortcut to the vbs file). You can control Excel from within the script allowing you to activate both answers.xls as well as the xls file dragged into the vbs file and use the data from the dropped file to update answer.xls. Steve "Beav" wrote in message ups.com... Let's say I have a spreadsheet(questions.xls) that represents a customer questionaire, formated in a fancy page layout. I will email that questionaire to 1000 people. They will fill it out and email it back to me. I have this other spreadsheet(answers.xls) that is just a table of data, representing the answers. I want to just drag the many returned questions files and drop one or more onto a batch file, or perhaps onto ahswers.xls itself, and have a macro copy over the data to the answers table and then leave the original in place. Any ideas? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Move-Drag and Drop Fails in Office 2007 Excel & Word | Excel Discussion (Misc queries) | |||
Drag/drop to open zipped Excel file | Excel Discussion (Misc queries) | |||
Batch Processing macro for excel | Excel Programming | |||
Drag and Drop file to open Excel (or MSWord | Excel Discussion (Misc queries) | |||
Open Batch File For Processing | Excel Programming |