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 bitch 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

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

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" wrote in message
...
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 bitch 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

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

"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" wrote in message
...
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 bitch 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

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

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" wrote in message
...


"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" wrote in message
...
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 bitch 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