Bahareh said:
Hello John
the reason that the setting was not accessable was that I always save a
version for myself and then try new macros (anyway the affects are unknown).
Thanks John For your guide, But problem stiil exist. Now an empty Excel
sheet opens and when I close it, It give me Error 424 and ask me to debug 2d
line of following set:
Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub
{{I don`t know VBA but I realy need to transfer hierarchy to Excel. Because
there are more than 2500 tasks in Pj plan which relate to all the departments
and some of them don`t know MSP but they change my MSP plan (sometimes they
change duration or even predecessors to reach their planned finish date! if
these happen, No variance report is needed!) . I don`t want to let them such
inlogical changes. That is why I want to transfer the tasks to Excel and give
them Excel Version.}}
I will be greatfull if you solve my Problem.
Thanks
Bahareh,
Well the good news is I have a solution for your real problem and it
doesn't involve VBA in any way. Now that you explained your end goal,
the main issue here is training for your users. We get a fair number of
posts similar to yours where someone who is responsible for project
management is trying to develop a method for keeping their users from
making unauthorized changes to Project files. Although there are ways to
incorporate auditing into the process (and auditing should be available
as a backup), by far the best solution to the problem is to adequately
train all Project users both on how to use Project and even more
importantly, on your corporate rules. For example, if users are informed
during training that certain Project fields are not to be edited because
the field is reserved for the project manager's use, then they have a
better understanding of what to do and what not to do. If some users
insist on making unauthorized changes then it is an issue to be brought
up with their supervision. Most employees want to do a good job and are
perfectly willing to follow the rules as long as those rules are clearly
laid out and are reasonable. Teamwork trumps conflict every time.
Granted, there may still be an occasional slip and a user may
inadvertently make an unauthorized change. That's why it is good
practice to have some auditing tools available. Most of the auditing can
be some with some fairly simple filters that can readily isolate
potential problem areas. More advanced auditing can be done with custom
VBA macros or by using the built-in utility, Compare Project Versions.
OK, that's the lecture. The problem you are having with Jack's code is
rather unusual. As far as I know, it has been used by a whole lot of
users with no issues. That makes me think that perhaps your setup is
unusual in some way. Since I can't replicate your problem I can only
guess at some things.
1. You mentioned that Excel does open but with a blank page. Is it
really blank? At the point of failure, there should be an entry in the
A1 cell, namely the filename of the project. Do you see that?
2. From the VB editor, go to Tools/References. There should be 4 or more
references checked. What are they (list each one fully)?
3. How exactly did you copy Jack's code into the VB editor? In other
words, if you show the Project Explorer, where is the module that
contains Jack's macro?
Let's see what you have for these questions. Then we can look as some
other things.
John
Project MVP
)