admin process to run macro

  • Thread starter Thread starter bonham28c
  • Start date Start date
B

bonham28c

Hi,

I need to set up a process on a windows xp PC to automatically copy
excel files from one folder to another and then run a macro inside
it. it needs to be run every day at mid night. But I do not know how
to set it up. could someone please help? thank you very much!

Eric
 
You need a few simple steps for this.

1. Set your macro to be run when the workbook is open using Private Sub
Workbook_Open()
Make sure your security settings are configured to allow macros to be run,
otherwise Excel will open, notice that macros are present and then wait for
user input before continuing.

2. Create an MS DOS Batch file to move your file and start the workbook.
Use your Windows Notepad to enter the DOS commands; they'll be something
like:

Copy C:\Book1.xls C:\Temp
C:\Temp\Book1.xls

Save the text file with the file extension .bat (batch file).
Test the batch file before moving on to step 3.

3. Goto the Windows Control Panel and open the Scheduled Tasks program.
Here's a link showing how to set up your scheduled task (the task is the
running of the batch file you created in step 2):

http://www.iopus.com/guides/winscheduler.htm

Thomas
 
thank you very much! one more thing though, I cannot set the macro to
run when opening the file. There are other users that uses this
file. is there a way to call a particular macro?
 
That's a good question.

The only idea I can think of is that the macro runs each time the work book
is opened, but quits without doing anything if the time is not between 23:45
and 00:15 (if your scheduled task runs at midnight).

You could include this check in your VB code; it should work unless your
colleagues are working very late at night or unless they leave the workbook
open.

Other users of this Newsgroup might have better ideas...
 
(e-mail address removed) wrote...
thank you very much!  one more thing though, I cannot set the macro
to run when opening the file.  There are other users that uses this
file.  is there a way to call a particular macro?
Click to expand...

The better approach would be to use a different workbook to hold the
macro(s) you need to run on moved/copied files. That macro (those
macros) would include statements like


Dim wb As Workbook, wbn As String
':
On Error Goto CleanUp
Application.EnableEvents = False
':
For Each wbn In some_array_of_filenames
Set wb = Workbooks.Open Filename:=wbn
With wb
'do whatever you need to do here
.Close SaveChanges:=True ' presumably you'd save your changes
End With
Next wbn
':
CleanUp:
Application.EnableEvents = True


Simplest to make this the Open event handler in this extra, server-
side workbook, and open it only after having copied/moved the files in
question.

As for determining the files in question, use xcopy rather than copy,
specifically, something like

del D:\est\ina\tion\filelist.txt
for /F "tokens=3 delims=>" %%a ^
in ('xcopy /s/f S:\our\ce*.xls D:\est\ina\tion\') do ^
echo %a >> D:\est\ina\tion\filelist.txt

then have your server-side workbook read the list of files to be
processed from the text file generated from the xcopy command, which
in the example above is D:\est\ina\tion\filelist.txt . You may or may
not want to use xcopy's /s switch (include subdirectories), but you
*DO* want to use its /f switch. Note also that the circumflexes ^ at
the end of the 2nd and 3rd lines above are MANDATORY as command
continuation characters.
 
Thanks a lot! interesting ideas. i would try both and see which one i
am able to implement. thanks!
 
Back
Top