FileOpen Stops My Macro

[egun]

In a VBA macro in Project 2003, I use the following to open a project file if
it is not open already. The 'prjFile' includes the full path name. For my
tests, I'm using files on my local harddrive. These files do have external
links.

If (Not prjIsOpen) Then
On Error GoTo Unable_tpen
Application.FileOpen Name:=prjFile, ReadOnly:=True, NoAuto:=True
On Error GoTo 0
End If

The FileOpen method opens the file with no problems, but the macro just
stops after that. No warnings or error messages. Any thoughts on why this
is happening?

Thanks,

Eric

 

[egun]

Sorry, John. I should have noted that after the macro opens the file, it
runs my Excel export code to export a bunch of data. I feed the macro a list
of files to process, and the macro is supposed to open each one and export
the data. It never gets past the first call to FileOpen.

When I say the macro stops, I mean that it never gets to the next statement,
even if I put breakpoints in all possible "next statements" in the code, and
there is no apparent error. Running the macro with the file already open
works fine.

 

[John]

In a VBA macro in Project 2003, I use the following to open a project file if
it is not open already. The 'prjFile' includes the full path name. For my
tests, I'm using files on my local harddrive. These files do have external
links.

If (Not prjIsOpen) Then
On Error GoTo Unable_tpen
Application.FileOpen Name:=prjFile, ReadOnly:=True, NoAuto:=True
On Error GoTo 0
End If

The FileOpen method opens the file with no problems, but the macro just
stops after that. No warnings or error messages. Any thoughts on why this
is happening?

Thanks,

Eric

Eric,
I'm a little confused. If the macro works, (i.e. opens the file), what
is the problem? There is nothing in your code snippit that says it
should do anything else. In the absence of a user message (e.g. "macro
complete") all macros will run and then finish "silently". That's the
idea.

John
Project MVP

 

[egun]

One other bit of information:

When I run the macro using some simple Project files I created just to test
it, it works fine. It's only when I try to open some of the "real", complex,
large files that the macro aborts.

Is there any sort of error trapping I can set up to determine why FileOpen
doesn't return to my macro?

 

[Ed Morrison]

Does the file that you are opening contain a macro? If so, if that macro
starts and has an End command, it may terminate all running macros.


--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[John]

One other bit of information:

When I run the macro using some simple Project files I created just to test
it, it works fine. It's only when I try to open some of the "real", complex,
large files that the macro aborts.

Is there any sort of error trapping I can set up to determine why FileOpen
doesn't return to my macro?

egun,
A few more words of explanation makes all the difference. At least now
your problem makes sense.

I can't recall every having a problem that strategically placed
breakpoints couldn't help the troubleshooting process, but I also use
the Immediate Window and one time (long ago) I resorted to the Locals
Window to see what was happening.

You mentioned that you put breakpoints on the "Next" statement. I don't
normally use the primary loop statements for breakpoints - too many
potential breaks and I generally want to know what is happening within
the loop. However it sounds like the code is "hanging" on the
Application.FileOpen... line with no error message. If the code seems to
work fine with small simple files but doesn't like more complex files, I
would try to find the reason. Perhaps expand the simple structure or
deflate the complex structure until you see a difference.

I know that's probably not much help, but without more detailed
information it is difficult to troubleshoot. Perhaps one of my fellow
MVPs has additional suggestions.

John
Project MVP

 

[egun]

I thought about that, and put NoAuto:=True in the FileOpen command to keep
any other macros from running.

The only clues I have gotten recently are:

One time, visual basic came back with an "Object Required" error during
FileOpen. I could not figure out the source of the error, except that when I
tried to examine Application.Projects(j), where j=1 (and I had an open
Project file), the watch window said the same "Object Required".

Also one time, after the FileOpen was done and the macro did not continue, I
got a dialog box in VB (actually two in succession) that said "An error
occurred while loading 'ThisProject'. Do you want to continue loading the
project?

That's all I know.

 

[Ed Morrison]

If you are getting an like that, it must be running code somewhere. Does
the project have a custom project guide associated with it? Does this error
occur on another machine? Can you open the project manually without a
problem? Are there any links in the project?

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[egun]

No custom project guides. I tried the macros on a couple of different
machines, and got the same results. For my tests, I'm trying to open three
fairly large (4000-6000 tasks each) Project files. One of them works, the
other two don't. There are no major differences between the three files in
terms of links, structure or content. I am able to open all three files
manually without any error messages. There are dozens of external links in
the files. I have tried opening with and without readonly. Nothing makes a
difference.

Very frustrating, because otherwise the macro is very cool, and it's a big
time saver for a lot of people around here.

 

[John]

No custom project guides. I tried the macros on a couple of different
machines, and got the same results. For my tests, I'm trying to open three
fairly large (4000-6000 tasks each) Project files. One of them works, the
other two don't. There are no major differences between the three files in
terms of links, structure or content. I am able to open all three files
manually without any error messages. There are dozens of external links in
the files. I have tried opening with and without readonly. Nothing makes a
difference.

Very frustrating, because otherwise the macro is very cool, and it's a big
time saver for a lot of people around here.

egun,
Another problem I have seen when a macro seems to be non-responsive is
that the macro is running fine but it is waiting for user input.
Unfortunately, another window has covered the message asking for user
input so it doesn't readily show and it makes the macro look like it got
lost. You mentioned that you have many external links. Do you also have
a separate resource pool or a master file? If either is present you may
be getting a message asking if you want to open all other files or to
update links, etc. I would think these messages would also show when the
files are opened manually, but maybe it works a little differently when
VBA is doing the opening or perhaps the sequence in which the files are
opened.

I would save the files to a separate folder so you can play with them.
If there is a pool file, try making sure it is opened before the sharer
files. If there is a master, open the subproject files first. If neither
of those are relevant, try breaking external links in groups. For
example, break half the links and see what happens. If no change, break
half the remaining, etc. If you do see a change when some of the links
are broken, iterate to find out which links are causing the problem.

Something else you might try - I've never done this so I don't know if
it will help. Create a simple Open Event macro that pops up a simple
message in each file (e.g. "xyz file is open"). It may just give you
some visual evidence as to how far the macro is getting as it opens each
project.

Hope this helps.
John
Project MVP

 

[egun]

Hate to keep dragging this out, but...

Something else you might try - I've never done this so I don't know if
it will help. Create a simple Open Event macro that pops up a simple
message in each file (e.g. "xyz file is open"). It may just give you
some visual evidence as to how far the macro is getting as it opens each
project.

Hope this helps.
John
Project MVP

John,

I added the Open and Activate events to both the Global project and the
problem file. When I open the problem file, I get no messages. When I open
other files, I get the Open event message followed by the Activate event
message - so I know it works. It seems that somewhere in the FileOpen
activity, the process is being aborted without telling me why. If I could
just get some clue about why I could fix it. Can error trapping help? I
have never used it but if it would help I can learn.

I haven't tried breaking links yet, but that will be next.

Eric

 

[egun]

Found it!

It became obvious as I started playing with _Open and _Activate events. I
began thinking about why these macros didn't run, and then about macro
security. When I checked, it was set to High, which only allows signed
macros from trusted sources, and automatically disables other macros. I
don't know why, but this had the effect of stopping my Excel export macro
cold each time I tried to open those files.

I tried various fixes, and found one that works. Now I use
'Application.AutomationSecurity = msoAutomationSecurityForceDisable' in my
macro, disable alerts, open the files READONLY, and everything works great.
I then reset the security to its previous value.

Thanks for all your suggestions. It all came down to Microsoft trying to
protect me from evil macros!

 

[John]

Found it!

It became obvious as I started playing with _Open and _Activate events. I
began thinking about why these macros didn't run, and then about macro
security. When I checked, it was set to High, which only allows signed
macros from trusted sources, and automatically disables other macros. I
don't know why, but this had the effect of stopping my Excel export macro
cold each time I tried to open those files.

I tried various fixes, and found one that works. Now I use
'Application.AutomationSecurity = msoAutomationSecurityForceDisable' in my
macro, disable alerts, open the files READONLY, and everything works great.
I then reset the security to its previous value.

Thanks for all your suggestions. It all came down to Microsoft trying to
protect me from evil macros!

egun,
Fantastic!! Now that you mention it, I'm surprised one of us didn't
suggest checking the security settings. I personally never use the
security feature (too much of a pain in the gluteus) but for those who
are a little less careful, it's probably a good feature.

John