Re: Exporting Hierarchy to Excel (Jack`s Macro)

In article <268BCFA8-EDAE-4086-88F4-37631192E2AB@xxxxxxxxxxxxx>,
Bahareh <Bahareh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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



"John" wrote:

In article <4B5BBB93-08DA-4AF2-B2D3-FD27B88EDE25@xxxxxxxxxxxxx>,
Bahareh <Bahareh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Oh yes. I forgot to do that so one problem solved. But the problem in
last
lines still exist:

Sub dwn (i As integer)
Set xlrow=xlrow.offset(i,o)
End sub

The error message mentioned:

"Run-time error (424)
Object Requierd"

What Can I do?

Thanks for your response

Bahareh,
Your original post specifically said that you set the reference to the
Excel object library so how did it get un-set? Once it is set for a
module, it doesn't need to be set again unless a new module is created.

With regard to your existing problem, the error message is pointing to
the fact that "xlrow" is not defined as a valid object. Try this, at the
very beginning of the code are the following two lines:
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range

Change those lines to the following:
Public xlRow As Excel.Range
Public xlCol As Excel.Range

Now try it. Does it work?

John
Project MVP



"Jan De Messemaeker" wrote:

Hi,

This one is easy, I'll take it straightaway
In the VB Editor, go to Tools, References, look for Microsoft Excel and
check the checkbox.
HTH

--
Jan De Messemaeker, Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
For FAQs: http://www.mvps.org/project/faqs.htm
"Bahareh" <Bahareh@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:E1C181D2-C4C2-417C-9BD1-7D393AB6D607@xxxxxxxxxxxxxxxx
Hello

I checked the code that I pasted into VB editor, all the lines exist
and
also the problem still exists. Another problem is occured in the line
following here:

Sub TaskHierarchy()
Dim xlApp As Excel.Application

Error says That it isn`t defiend.

what is wrong with my Pj Plan? What can I do?

Thanks

"John" wrote:

In article <1B22FE7C-7552-4D1C-9210-3F66436F85FF@xxxxxxxxxxxxx>,
Bahareh <Bahareh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello

I need to export hierarchy to excel. I used Jack Dahlgren`s VBA
macro
but it
doesn`t work. (I cheched Microsoft excel library ... in
resource/tools
menue).
It shows a message box which say:

"
Run-time error (424)
Object Requierd
"
In debug window The line which is shown yellow is in the last
lines.

"
Sub dwn (i As integer)
Set xlrow=xlrow.offset(i,o)
End sub
"

I selected the tasks and runed the Macro but The problem still
exist.
Can anybody help me? Jack may you solve the problem?

Thanks

Bahareh,
Jack's code does run - I just tried it myself. One thing I did note
however is that when I copied and pasted the code from Jack's
website
into the VB Editor, I had to do some editing to satisfy the
complier.
The copy and paste process apparently introduced some hidden tabs
or
other characters that had to be edited out. Whenever code is edited
like
that it is fairly easy to inadvertently delete a line of code. It
sounds
like that is what happened to you. I would check the code in your
VB
editor and make sure it has all the lines that are shown on Jack's
website.

John
Project MVP





.