Excel Automation

[jdlester]

I am looking to automate a couple of tasks for an Excel workbook
stored on a mapped network drive that will be accessed and shared by
multiple users. A list of these tasks is as follows:

1) Automatically execute a macro in the workbook every night at 12:15
AM. My initial thoughts are to write a VBScript file that will create
an instance of Excel, open the workbook and execute the macro. The
VBScript will be run using Windows Scheduler on the workbook's host
computer.

2) Allow users to open the Excel workbook from within a 3rd party
application, make changes if necessary and save the file. (Note that
the 3rd party application is built on top of VBA, which I already have
the code for opening the file.) My problem is how to give multiple
users the ability to open the file, edit and save changes. My first
inclination is to copy the file to the user's local hard drive and
then open it. The problem then becomes if the user makes a change and
saves it the file will still be on their local hard drive and not on
the network drive for other users to access. The only way I can think
of to do this is to a special macro for saving the file back to the
network drive.

Any help is greatly appreciated.

Thanks.

-Jim

 

[Jim Thomlinson]

Multiple users accessing a spreadsheet is problematic at the best of times.
While you can create a shared workbook the limitation that that imposes on
the workbook are many. Before you head down that road do some research. Most
of the experienced users that I know do not create shared workbooks. They
tend to be more trouble than they are worth. I would be inclined to use some
kind of a system that handles concurrent users. Perhaps store all of your
data in a back end Access database which will handle concurrent users. If you
want to use Excel as your front end that can be done.

What I am saying is that if you intend to use XL for what you have described
I wish you the best of luck because you are going to need it...

As for your first question you can create some on open code that checks the
time of day. If it is between 12PM and 1AM then have it do some processing.
Now all you need to do is to place a shor tcut to the file in your Windows
scheduler and you are all set to go.