Macro from a different xls file run, still says file in use

Hello,

my .xls file runs a macro from a different file via
"'M:\month\2000\kkhelp.xls'!project.project"

Usually, when the macro has done its magic (exporting data etc), the macro
saves the .xls file and closes it (and itself with it, of course). If the
user hasnt inputted enough data to export, the macro tells the user that and
ends via "exit sub". The problem is, that if this case occurs, the kkhelp.xls
file is still open, even if no macros are running anymore. If someone else
tries to run the macro while the first user is fixing his input data, the
macro will ***** about the kkhelp.xls file being in use.

Is there any way to stop this from happening, for example automatically
close the kkhelp.xls when there are no macros running?

Help appreciated,
Amos

Rather than using exit sub by itself

Rather than (this is pseudo code)

if not enough data then
msgbox "not enough"
exit sub
End if

use something like

If not enough data then
msgbox "not enough"
Close the kkelp.xls
end if

Assuming kkhelp.xls is the workbook containing the macro
as soon as the workbook closes, the macro halts.

--
Regards,
Tom Ogilvy


"A. Ahola" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> my .xls file runs a macro from a different file via
> "'M:\month\2000\kkhelp.xls'!project.project"
>
> Usually, when the macro has done its magic (exporting data etc), the macro
> saves the .xls file and closes it (and itself with it, of course). If the
> user hasnt inputted enough data to export, the macro tells the user that
and
> ends via "exit sub". The problem is, that if this case occurs, the
kkhelp.xls
> file is still open, even if no macros are running anymore. If someone else
> tries to run the macro while the first user is fixing his input data, the
> macro will ***** about the kkhelp.xls file being in use.
>
> Is there any way to stop this from happening, for example automatically
> close the kkhelp.xls when there are no macros running?
>
> Help appreciated,
> Amos

"Tom Ogilvy" wrote:

> Rather than using exit sub by itself
>
> Rather than (this is pseudo code)
>
> if not enough data then
> msgbox "not enough"
> exit sub
> End if
>
> use something like
>
> If not enough data then
> msgbox "not enough"
> Close the kkelp.xls
> end if
>
> Assuming kkhelp.xls is the workbook containing the macro
> as soon as the workbook closes, the macro halts.
>
> --
> Regards,
> Tom Ogilvy

Makes sense, I thought that whenever a file with macro references to a
another file is opened, the other files are opened as well. This isnt the
case, the other files are opened when the macros are called, and the macro
itself must handle closing the file with the macro.

Anyway, your way worked, thanks for the help.

-Amos

>
>
> "A. Ahola" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > my .xls file runs a macro from a different file via
> > "'M:\month\2000\kkhelp.xls'!project.project"
> >
> > Usually, when the macro has done its magic (exporting data etc), the macro
> > saves the .xls file and closes it (and itself with it, of course). If the
> > user hasnt inputted enough data to export, the macro tells the user that
> and
> > ends via "exit sub". The problem is, that if this case occurs, the
> kkhelp.xls
> > file is still open, even if no macros are running anymore. If someone else
> > tries to run the macro while the first user is fixing his input data, the
> > macro will ***** about the kkhelp.xls file being in use.
> >
> > Is there any way to stop this from happening, for example automatically
> > close the kkhelp.xls when there are no macros running?
> >
> > Help appreciated,
> > Amos
>
>
>

If you actually create a reference to the file (in the VBE) in
Tools=>References, then the referenced workbook is opened with the
referencing file, but not just because you refer to it in your code.

--
Regards,
Tom Ogilvy


"A. Ahola" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Tom Ogilvy" wrote:
>
> > Rather than using exit sub by itself
> >
> > Rather than (this is pseudo code)
> >
> > if not enough data then
> > msgbox "not enough"
> > exit sub
> > End if
> >
> > use something like
> >
> > If not enough data then
> > msgbox "not enough"
> > Close the kkelp.xls
> > end if
> >
> > Assuming kkhelp.xls is the workbook containing the macro
> > as soon as the workbook closes, the macro halts.
> >
> > --
> > Regards,
> > Tom Ogilvy
>
> Makes sense, I thought that whenever a file with macro references to a
> another file is opened, the other files are opened as well. This isnt the
> case, the other files are opened when the macros are called, and the macro
> itself must handle closing the file with the macro.
>
> Anyway, your way worked, thanks for the help.
>
> -Amos
>
> >
> >
> > "A. Ahola" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > my .xls file runs a macro from a different file via
> > > "'M:\month\2000\kkhelp.xls'!project.project"
> > >
> > > Usually, when the macro has done its magic (exporting data etc), the
macro
> > > saves the .xls file and closes it (and itself with it, of course). If
the
> > > user hasnt inputted enough data to export, the macro tells the user
that
> > and
> > > ends via "exit sub". The problem is, that if this case occurs, the
> > kkhelp.xls
> > > file is still open, even if no macros are running anymore. If someone
else
> > > tries to run the macro while the first user is fixing his input data,
the
> > > macro will ***** about the kkhelp.xls file being in use.
> > >
> > > Is there any way to stop this from happening, for example
automatically
> > > close the kkhelp.xls when there are no macros running?
> > >
> > > Help appreciated,
> > > Amos
> >
> >
> >