Batch File drag-n-drop processing.

[Beav]

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.

 

[JLatham]

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>

 

[JLatham]

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.

 

[Steve Yandl]

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