Jack heirarchy macro - adding excel formatting steps

[James]

Hi,
I'm developing some extra coding onto the end of Jack's Heirarchy macro with
some formatting steps so that I do not have to format the spreadsheet every
tme i run the macro.

I have come up against a little bit of an annoyance, though, and as I'm no
expert, would like some help in resolving it.

The first line of code I have added is:

Range("A:A").Select

The first time I run the code I get no errors. The second time I run the
code, I get the following error:

Run Time Error '1004':
Method 'Range' of Object '_Global' failed

When I debug, the first line of my code above is highlighted.

If I end, and run a third time, it runs ok. But every alternate time I run
the code, I get the same error.

I have selected the Excel object library reference, else it wouldn't run at
all.

Can anyone point me towards why I get this error?

Thanks
James.

 

[salgud]

Hi,
I'm developing some extra coding onto the end of Jack's Heirarchy macro with
some formatting steps so that I do not have to format the spreadsheet every
tme i run the macro.

I have come up against a little bit of an annoyance, though, and as I'm no
expert, would like some help in resolving it.

The first line of code I have added is:

Range("A:A").Select

The first time I run the code I get no errors. The second time I run the
code, I get the following error:

Run Time Error '1004':
Method 'Range' of Object '_Global' failed

When I debug, the first line of my code above is highlighted.

If I end, and run a third time, it runs ok. But every alternate time I run
the code, I get the same error.

I have selected the Excel object library reference, else it wouldn't run at
all.

Can anyone point me towards why I get this error?

Thanks
James.

You've posted to the M$ Project forum. You'll probably get better results
posting to microsoft.public.excel.programming

 

[James]

Thanks salgud,

I did wonder about posting this there originally, as the code relates to
Excel. However, the code is written in VBA in Microsoft Project, and run from
there, and so figured I'd only get bounced to here anyway! My thoughts were
that it was something peculiar to running out of Project.

I'll give it a shot over there.

Thanks.
James.

 

[John]

Thanks salgud,

I did wonder about posting this there originally, as the code relates to
Excel. However, the code is written in VBA in Microsoft Project, and run from
there, and so figured I'd only get bounced to here anyway! My thoughts were
that it was something peculiar to running out of Project.

I'll give it a shot over there.

Thanks.
James.

James,
It might help to see more details of the code you added to Jack's macro.

John
Project MVP

 

[Gary L. Chefetz]

Actually, the appropriate ng would be microsoft.public.project.developer

 

[Jack Dahlgren MVP]

Hmmm...

Haven't looked at that code for a while, but I'm imagining that you are
running this line when there is no instance of excel open.
Posting the code is the only way to know what is going on here.
And the others are correct that this should be in the
microsoft.public.project.developer newsgroup.
-Jack

 

[Jan De Messemaeker]

Hi,


It could be very very simple; I've had this once
Range is also a Project object.
Try Excel.range, or use range as a property of an Excel object, see what
happens
HTH
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Hi,
I'm developing some extra coding onto the end of Jack's Heirarchy macro
with
s

ome formatting steps so that I do not have to format the spreadsheet every

 

[James]

Thanks Jack, John, Gary and Jan for your feedback.

I currently have this code added just before it kicks back to Project to
show the message box.

'Delete first column
Range("A:A").Select
Selection.Delete Shift:=xlToLeft

which deletes the blank '0' column. It is the 'Range' line that kicks out
every other time it runs.

Jack, excel is open at this point, as your Macro has already started and
populated a sheet with my Project data. However, the hidden "Personal.xls" is
not open at this point. I originally created a Macro in Excel, but had to
save, close and re-open before I could use it.

Jan, you could be right, here, and I wondered if it might be something like
this. But why whould it work every other time it is run? If it was this,
would it fail every time? I will try the change you suggest and see if the
issue continues.

As to which section this should be in, I already have this same question in
two forums here. I put it here originally as this has originated from VBA
used in the desktop version of Project, not developer. I choose not to post
it in the Excel board originally as I felt the problem arose due to the fact
that the VBA coding is in MSP, and I wouldn't get the same issue if this code
was solely in Excel.

One contributing factor could be that I am using Project 2007 with Office
2003?

Also, when I check the references, I see V5 of the Excel object library for
"Global.MPT", and V11 for my current project file. Should they both be V11?

Thanks for your help!

James.

 

[James]

Thanks Jan,

As per my previous reply to Jack, I have tried changing from:

Range("A:A").Select

to

Excel.Range("A:A").Select

but it still give the same run time error every second time I run the macro.

Sorry, my VBA skills are limited. How do I use range as a property of an
Excel object?

Thanks.

 

[Jack Dahlgren MVP]

The Excel library for excel 2003 should be v11, 2007 is v12.

Like Jan said, try fully qualifying the method (ie Excel.Range instead of
Range).
The other thing to look at is whether there is a sheet which is active. With
no active sheet the range doesn't mean anything.

-Jack Dahlgren

 

[James]

Thanks Jack,

When I click on my current project in the top left window and view
references, it is V11. When I click on global and view references I only see
V5. This is in the Project VBA session. In Excel it is V11. Given that the
error I get references '_Global', this seems to me to be theprime culprit.

I have changed all of my Range statements into Excel.Range, but it has not
fixed the problem.

I have not added a specific line to activate the sheet. The code follows
directly from your heirarch macro (before it throws back to Project), and I
always close down Excel before I run it, so I am always in the same state
when I start.

If the problem was due to the sheet not being active, the problem would
happen every time, wouldn't it? I have this issue every other time it starts.
Here is the full code, including the Heirarchy macro which I have made a
couple of tweaks to.

Sub TaskHierarchy()
Dim xlApp As ......
......Dim Acount As Integer

Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = ActiveProject.Name

'count columns needed
ColumnCount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel > ColumnCount Then
ColumnCount = t.OutlineLevel
End If
End If
Next t

'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
'xlRow = "Filename: " & ActiveProject.Name <---I don't want this info on the
sheet.
'dwn 1
'xlRow = "OutlineLevel"
'dwn 1

'label Columns
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "#"
rgt 1
xlCol = "Duration"
rgt 1
xlCol = "Start"
rgt 1
xlCol = "Finish"
rgt 1
xlCol = "Predecessors"
rgt 1
xlCol = "Business Function"
rgt 1
xlCol = "Resource"
rgt -7 - ColumnCount 'Dependant on column count
xlCol = "Task Name" <--- Added to complete header row.
dwn 1
Tcount = 0
Acount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
'dwn 1 <--- Did not match how I want it formatted
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
dwn 1
End If
For Each Asgn In t.Assignments
'dwn 1 <--- Did not match how I want it formatted.
Set xlCol = xlRow.Offset(0, Columns)
If Acount = 0 Then
xlCol = Asgn.TaskID
rgt 1
xlCol = (Asgn.Work / 480) & " Days"
rgt 1
xlCol = Asgn.Start
rgt 1
xlCol = Asgn.Finish
rgt 1
xlCol = t.Predecessors
rgt 1
xlCol = t.Text1
rgt 1
Else <--- For loop added in to format where more than one
resource is against a task
rgt 6
End If
xlCol = Asgn.ResourceName
dwn 1
Acount = Acount + 1
Next Asgn
Tcount = Tcount + 1
Acount = 0
End If
Next t

'Delete first column <----Start of my formatting routine
Excel.Range("A:A").Select <---- Method 'Range' of object '_Global' failed
Selection.Delete Shift:=xlToLeft
'Delete first blank
Selection.Offset(0, ColumnCount).Range("A:A").Select
Selection.Delete Shift:=xlToLeft
'Format task number
Selection.Cut
Excel.Range("A:A").Select
Selection.Insert Shift:=xlToRight
Excel.Range("A:A").EntireColumn.AutoFit
Excel.Range("A:A").HorizontalAlignment = xlCenter
'Clear column numbers
Excel.Range(Cells(1, 3), Cells(1, ColumnCount + 1)).Clear
'Resizes task columns
Excel.Range(Cells(1, 2), Cells(1, ColumnCount)).ColumnWidth = 2.14
Selection.Offset(0, ColumnCount).EntireColumn.Select
Selection.EntireColumn.AutoFit
'Sort out predecessor column
Selection.Offset(0, 4).EntireColumn.Select
Selection.ColumnWidth = 13.57
Selection.HorizontalAlignment = xlLeft
'Format start and finish dates
Excel.Range(Cells(1, ColumnCount + 3), Cells(1, ColumnCount +
4)).EntireColumn.Select
Selection.NumberFormat = "d-mmm-yy"
Selection.HorizontalAlignment = xlCenter
Selection.EntireColumn.AutoFit
'Adjust column width for last two columns
Selection.Offset(0, 3).EntireColumn.Select
Selection.EntireColumn.AutoFit
'Merges task name cells
Excel.Range(Cells(1, 2), Cells(1, ColumnCount + 1)).Select
Selection.Merge
'Makes header row bold
Rows("1:1").Select
Selection.Font.Bold = True

'AppActivate "Microsoft Project"
MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
End Sub

Sorry for the length, and thanks for the help!
James