How to read Enterprise Outline Code values in VBA

C

Coolpran

Hi

Let me start by saying that I am new to Project Server and the whole EPM
phenomenon.

However, I am working with Project Server 2003 and I need to do develop a
custom VBA form in the Enterprise Global.

I have set up lookup tables for several Custom Outline Codes in the
Enterprise Global. By Custom Outline Code I mean the ones you set up by
going to Tools->Customize->Enterprise Fields->Custom Outline Codes ->Choose
Project Radio button

Now, in my VBA Form, I want to populate a dropdown with values from the
lookup tables above.
I found that I could do it by reading the Application.GlobalOutlineCodes,
then loop through all the outline codes till I find a match and then read
the lookup table values.
The code I am using is:
'----- code starts here ----
Dim oc As OutlineCode
Dim lt As LookupTable
Dim lte As LookupTableEntry
Dim entOutlineCodes As OutlineCodes
Dim OLcode As Long
Dim myCount As Integer
myCount = 0
EntOLCode14 = 188744602 ' Value for
pjTaskEnterpriseProjectOutlineCode14 "Task Enterprise Project Outline
Code14"

Set entOutlineCodes = Application.GlobalOutlineCodes

For Each oc In entOutlineCodes
OLcode = oc.FieldID

Select Case OLcode
Case EntOLcode14 ' Proj Mgr
Set lt = oc.LookupTable
Debug.Print "--Start of Project Manager loop at " & Now &
"---"
For Each lte In lt
cboProjMgr.AddItem lte.FullName
Next
cboProjMgr.Value =
ActiveProject.ProjectSummaryTask.GetField(pjTaskEnterpriseProjectOutlineCode14)
txtProjMgr.Value = cboProjMgr.Value
... other case statements
End Select

Next
'----- code ends here -----

The For loop is taking a lot of time to execute.
Is there an elegant solution which will allow me to read just the required
outline code without the loop?

I searched on Google Groups and found a similar answer posted by Evgeniy
(http://groups.google.com/group/micr...ee0795bb7e3/23460d81b74e1d9a#23460d81b74e1d9a)
The code he mentioned was
'---- code starts here -----
Dim tsk As msproject.Task
MsgBox (tsk.GetField(pjTaskEnterpriseProjectOutlineCode14))
'---- code ends here ------

But this is not working for me. Its giving an error "Object variable or With
block variable not set"

Also the MSDN documentation on this topic is very sparse.
Can anyone please help.

Thanks
Coolpran
 
R

Rod Gill

Hi,

Project codes can only be read from the Project SummaryTask:
Activeproject.projectsummarytask.EnterpriseProjectOutlineCode14

The same for a task is something like:
tsk.EnterpriseTaskOutlineCode14
 
C

coolpran

Thanks Rod

But "ActiveProject.ProjectSummaryTask.EnterpriseProjectOutlineCode14"
will give me the value of that field in the current project.
I want to fill the dropdown with all possible values for the
"EnterpriseProjectOutlineCode14" field as descrbed in the Enterprise
Global lookup for that field

Any ideas?
 
R

Rod Gill

Read the srvrdb.htm file, it describes all available fields in the SQL
Server tables. You can read the data directly.
 
M

Mike Glen

Hi Coolpran ,

Next time, try posting on the server newsgroup. Please see FAQ Item: 24.
Project Newsgroups. FAQs, companion products and other useful Project
information can be seen at this web address:
http://project.mvps.org/faqs.htm

Mike Glen
Project MVP
 
R

Ray McCoppin

You can read the Outlinecodes lookup table(See the code below), but I have
found this only works if the Enterprise Global is open in Project Pro.

Sub test()
Dim MyOC As OutlineCode
For Each MyOC In Application.ActiveProject.OutlineCodes
If MyOC.FieldID =
PjCustomField.pjCustomProjectEnterpriseOutlineCode2 Then
Debug.Print "Required = " & MyOC.RequiredCode
Debug.Print "Only Complete Codes = " & MyOC.OnlyCompleteCodes
Debug.Print "Only LookUp Table Codes = " &
MyOC.OnlyLookUpTableCodes
Debug.Print "Only Leaves = " & MyOC.OnlyLeaves
Debug.Print "Only Complete Codes = " & MyOC.OnlyCompleteCodes
For x = 1 To MyOC.LookupTable.Count
Debug.Print "Level = " & MyOC.LookupTable.Item(x).Level
Debug.Print "FullName = " & MyOC.LookupTable.Item(x).FullName
Debug.Print "FullName = " &
MyOC.LookupTable.Item(x).Description
Next x
End If
Next MyOC
End Sub
 
W

William Raymond

Hi Coolpran,

You can use the approaches listed here in this document, but there is a
whole different approach you can take here.

Use the PDS (Project Data Services) to call them with a SOAP call in your
VBA code. You can use the following methods to achieve your goals:

EnterpriseOutlineCodes Method
EnterpriseOutlineCodesHierarchical Method
EnterpriseOutlineCodeUpdate Method
OutlineCodeAddValues Method
OutlineCodeDeleteValues Method
OutlineCodeUpdateValues Method

You can get the Project Server 2003 SDK, along with the PDS online help and
code samples here:
http://www.microsoft.com/downloads/...8c-8bca-4db9-8753-178c0d3099c5&DisplayLang=en

-Bill
 
C

Coolpran

Ray,

This approach won't work as it needs Enterprise Global to be open in Project
Pro.

Thanks anyways.
 
C

Coolpran

William

Thanks for the info.
I am not sure how to use the PDS & SOAP calls via VBA in Enterprise Global,
but I will give it a try.

Will get back once I have tried

Coolpran

William Raymond said:
Hi Coolpran,

You can use the approaches listed here in this document, but there is a
whole different approach you can take here.

Use the PDS (Project Data Services) to call them with a SOAP call in your
VBA code. You can use the following methods to achieve your goals:

EnterpriseOutlineCodes Method
EnterpriseOutlineCodesHierarchical Method
EnterpriseOutlineCodeUpdate Method
OutlineCodeAddValues Method
OutlineCodeDeleteValues Method
OutlineCodeUpdateValues Method

You can get the Project Server 2003 SDK, along with the PDS online help and
code samples here:
http://www.microsoft.com/downloads/...8c-8bca-4db9-8753-178c0d3099c5&DisplayLang=en

-Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Coolpran said:
Hi

Let me start by saying that I am new to Project Server and the whole EPM
phenomenon.

However, I am working with Project Server 2003 and I need to do develop a
custom VBA form in the Enterprise Global.

I have set up lookup tables for several Custom Outline Codes in the
Enterprise Global. By Custom Outline Code I mean the ones you set up by
going to Tools->Customize->Enterprise Fields->Custom Outline
Codes ->Choose Project Radio button

Now, in my VBA Form, I want to populate a dropdown with values from the
lookup tables above.
I found that I could do it by reading the Application.GlobalOutlineCodes,
then loop through all the outline codes till I find a match and then read
the lookup table values.
The code I am using is:
'----- code starts here ----
Dim oc As OutlineCode
Dim lt As LookupTable
Dim lte As LookupTableEntry
Dim entOutlineCodes As OutlineCodes
Dim OLcode As Long
Dim myCount As Integer
myCount = 0
EntOLCode14 = 188744602 ' Value for
pjTaskEnterpriseProjectOutlineCode14 "Task Enterprise Project Outline
Code14"

Set entOutlineCodes = Application.GlobalOutlineCodes

For Each oc In entOutlineCodes
OLcode = oc.FieldID

Select Case OLcode
Case EntOLcode14 ' Proj Mgr
Set lt = oc.LookupTable
Debug.Print "--Start of Project Manager loop at " & Now &
"---"
For Each lte In lt
cboProjMgr.AddItem lte.FullName
Next
cboProjMgr.Value =
ActiveProject.ProjectSummaryTask.GetField(pjTaskEnterpriseProjectOutlineCode14)
txtProjMgr.Value = cboProjMgr.Value
... other case statements
End Select

Next
'----- code ends here -----

The For loop is taking a lot of time to execute.
Is there an elegant solution which will allow me to read just the required
outline code without the loop?

I searched on Google Groups and found a similar answer posted by Evgeniy
(http://groups.google.com/group/micr...ee0795bb7e3/23460d81b74e1d9a#23460d81b74e1d9a)
The code he mentioned was
'---- code starts here -----
Dim tsk As msproject.Task
MsgBox (tsk.GetField(pjTaskEnterpriseProjectOutlineCode14))
'---- code ends here ------

But this is not working for me. Its giving an error "Object variable or
With block variable not set"

Also the MSDN documentation on this topic is very sparse.
Can anyone please help.

Thanks
Coolpran
Click to expand...
Click to expand...
 
W

William Raymond

Just as an FYI (and I could very well be mistaken here), I believe the Six
Sigma Accelerator continas some VB automation code that connects to the
PDS. -Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Coolpran said:
William

Thanks for the info.
I am not sure how to use the PDS & SOAP calls via VBA in Enterprise
Global,
but I will give it a try.

Will get back once I have tried

Coolpran

William Raymond said:
Hi Coolpran,

You can use the approaches listed here in this document, but there is a
whole different approach you can take here.

Use the PDS (Project Data Services) to call them with a SOAP call in your
VBA code. You can use the following methods to achieve your goals:

EnterpriseOutlineCodes Method
EnterpriseOutlineCodesHierarchical Method
EnterpriseOutlineCodeUpdate Method
OutlineCodeAddValues Method
OutlineCodeDeleteValues Method
OutlineCodeUpdateValues Method

You can get the Project Server 2003 SDK, along with the PDS online help
and
code samples here:
http://www.microsoft.com/downloads/...8c-8bca-4db9-8753-178c0d3099c5&DisplayLang=en

-Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Coolpran said:
Hi

Let me start by saying that I am new to Project Server and the whole
EPM
phenomenon.

However, I am working with Project Server 2003 and I need to do develop
a
custom VBA form in the Enterprise Global.

I have set up lookup tables for several Custom Outline Codes in the
Enterprise Global. By Custom Outline Code I mean the ones you set up by
going to Tools->Customize->Enterprise Fields->Custom Outline
Codes ->Choose Project Radio button

Now, in my VBA Form, I want to populate a dropdown with values from the
lookup tables above.
I found that I could do it by reading the
Application.GlobalOutlineCodes,
then loop through all the outline codes till I find a match and then
read
the lookup table values.
The code I am using is:
'----- code starts here ----
Dim oc As OutlineCode
Dim lt As LookupTable
Dim lte As LookupTableEntry
Dim entOutlineCodes As OutlineCodes
Dim OLcode As Long
Dim myCount As Integer
myCount = 0
EntOLCode14 = 188744602 ' Value for
pjTaskEnterpriseProjectOutlineCode14 "Task Enterprise Project Outline
Code14"

Set entOutlineCodes = Application.GlobalOutlineCodes

For Each oc In entOutlineCodes
OLcode = oc.FieldID

Select Case OLcode
Case EntOLcode14 ' Proj Mgr
Set lt = oc.LookupTable
Debug.Print "--Start of Project Manager loop at " & Now
&
"---"
For Each lte In lt
cboProjMgr.AddItem lte.FullName
Next
cboProjMgr.Value =
ActiveProject.ProjectSummaryTask.GetField(pjTaskEnterpriseProjectOutlineCode14)
txtProjMgr.Value = cboProjMgr.Value
... other case statements
End Select

Next
'----- code ends here -----

The For loop is taking a lot of time to execute.
Is there an elegant solution which will allow me to read just the
required
outline code without the loop?

I searched on Google Groups and found a similar answer posted by
Evgeniy
(http://groups.google.com/group/micr...ee0795bb7e3/23460d81b74e1d9a#23460d81b74e1d9a)
The code he mentioned was
'---- code starts here -----
Dim tsk As msproject.Task
MsgBox (tsk.GetField(pjTaskEnterpriseProjectOutlineCode14))
'---- code ends here ------

But this is not working for me. Its giving an error "Object variable or
With block variable not set"

Also the MSDN documentation on this topic is very sparse.
Can anyone please help.

Thanks
Coolpran
Click to expand...
Click to expand...
Click to expand...
 
K

Krishna Alavala

Hi

If you can connect to the SQL Server database from VBA macro try the
following query to get the outline codes drop down values

-- Project Enterprise Outline codes. List of possible values (188744589 and
188744618)
select oc_cached_full_name from msp_outline_codes
where proj_id = (select proj_id from msp_projects where proj_type = 2)
and oc_field_id = 188744589

Best of luck
Krishna

William Raymond said:
Just as an FYI (and I could very well be mistaken here), I believe the Six
Sigma Accelerator continas some VB automation code that connects to the
PDS. -Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Coolpran said:
William

Thanks for the info.
I am not sure how to use the PDS & SOAP calls via VBA in Enterprise
Global,
but I will give it a try.

Will get back once I have tried

Coolpran

William Raymond said:
Hi Coolpran,

You can use the approaches listed here in this document, but there is a
whole different approach you can take here.

Use the PDS (Project Data Services) to call them with a SOAP call in your
VBA code. You can use the following methods to achieve your goals:

EnterpriseOutlineCodes Method
EnterpriseOutlineCodesHierarchical Method
EnterpriseOutlineCodeUpdate Method
OutlineCodeAddValues Method
OutlineCodeDeleteValues Method
OutlineCodeUpdateValues Method

You can get the Project Server 2003 SDK, along with the PDS online help
and
code samples here:
http://www.microsoft.com/downloads/...8c-8bca-4db9-8753-178c0d3099c5&DisplayLang=en

-Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Hi

Let me start by saying that I am new to Project Server and the whole
EPM
phenomenon.

However, I am working with Project Server 2003 and I need to do develop
a
custom VBA form in the Enterprise Global.

I have set up lookup tables for several Custom Outline Codes in the
Enterprise Global. By Custom Outline Code I mean the ones you set up by
going to Tools->Customize->Enterprise Fields->Custom Outline
Codes ->Choose Project Radio button

Now, in my VBA Form, I want to populate a dropdown with values from the
lookup tables above.
I found that I could do it by reading the
Application.GlobalOutlineCodes,
then loop through all the outline codes till I find a match and then
read
the lookup table values.
The code I am using is:
'----- code starts here ----
Dim oc As OutlineCode
Dim lt As LookupTable
Dim lte As LookupTableEntry
Dim entOutlineCodes As OutlineCodes
Dim OLcode As Long
Dim myCount As Integer
myCount = 0
EntOLCode14 = 188744602 ' Value for
pjTaskEnterpriseProjectOutlineCode14 "Task Enterprise Project Outline
Code14"

Set entOutlineCodes = Application.GlobalOutlineCodes

For Each oc In entOutlineCodes
OLcode = oc.FieldID

Select Case OLcode
Case EntOLcode14 ' Proj Mgr
Set lt = oc.LookupTable
Debug.Print "--Start of Project Manager loop at " & Now
&
"---"
For Each lte In lt
cboProjMgr.AddItem lte.FullName
Next
cboProjMgr.Value =
ActiveProject.ProjectSummaryTask.GetField(pjTaskEnterpriseProjectOutlineCode14)
txtProjMgr.Value = cboProjMgr.Value
... other case statements
End Select

Next
'----- code ends here -----

The For loop is taking a lot of time to execute.
Is there an elegant solution which will allow me to read just the
required
outline code without the loop?

I searched on Google Groups and found a similar answer posted by
Evgeniy
(http://groups.google.com/group/micr...ee0795bb7e3/23460d81b74e1d9a#23460d81b74e1d9a)
The code he mentioned was
'---- code starts here -----
Dim tsk As msproject.Task
MsgBox (tsk.GetField(pjTaskEnterpriseProjectOutlineCode14))
'---- code ends here ------

But this is not working for me. Its giving an error "Object variable or
With block variable not set"

Also the MSDN documentation on this topic is very sparse.
Can anyone please help.

Thanks
Coolpran
Click to expand...
Click to expand...
Click to expand...
 
C

Coolpran

Thanks for your suggestion Krishna.

But connecting to SQL server via VBA macro means I will have to hardcode SQL
username/password within the macro, or set-up windows authentication for SQL
access for each user.
And I am pretty sure both of these will be a big no-no from our IT guys who
as you know are paranoid when it comes to security risks

Thanks anyways
Coolpran



Krishna Alavala said:
Hi

If you can connect to the SQL Server database from VBA macro try the
following query to get the outline codes drop down values

-- Project Enterprise Outline codes. List of possible values (188744589
and
188744618)
select oc_cached_full_name from msp_outline_codes
where proj_id = (select proj_id from msp_projects where proj_type = 2)
and oc_field_id = 188744589

Best of luck
Krishna

William Raymond said:
Just as an FYI (and I could very well be mistaken here), I believe the
Six
Sigma Accelerator continas some VB automation code that connects to the
PDS. -Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Coolpran said:
William

Thanks for the info.
I am not sure how to use the PDS & SOAP calls via VBA in Enterprise
Global,
but I will give it a try.

Will get back once I have tried

Coolpran

:

Hi Coolpran,

You can use the approaches listed here in this document, but there is
a
whole different approach you can take here.

Use the PDS (Project Data Services) to call them with a SOAP call in
your
VBA code. You can use the following methods to achieve your goals:

EnterpriseOutlineCodes Method
EnterpriseOutlineCodesHierarchical Method
EnterpriseOutlineCodeUpdate Method
OutlineCodeAddValues Method
OutlineCodeDeleteValues Method
OutlineCodeUpdateValues Method

You can get the Project Server 2003 SDK, along with the PDS online
help
and
code samples here:
http://www.microsoft.com/downloads/...8c-8bca-4db9-8753-178c0d3099c5&DisplayLang=en

-Bill

--
William Raymond [MS Project MVP]
(e-mail address removed)
BLOG: http://www.mympa.org/blog.aspx


Hi

Let me start by saying that I am new to Project Server and the whole
EPM
phenomenon.

However, I am working with Project Server 2003 and I need to do
develop
a
custom VBA form in the Enterprise Global.

I have set up lookup tables for several Custom Outline Codes in the
Enterprise Global. By Custom Outline Code I mean the ones you set up
by
going to Tools->Customize->Enterprise Fields->Custom Outline
Codes ->Choose Project Radio button

Now, in my VBA Form, I want to populate a dropdown with values from
the
lookup tables above.
I found that I could do it by reading the
Application.GlobalOutlineCodes,
then loop through all the outline codes till I find a match and then
read
the lookup table values.
The code I am using is:
'----- code starts here ----
Dim oc As OutlineCode
Dim lt As LookupTable
Dim lte As LookupTableEntry
Dim entOutlineCodes As OutlineCodes
Dim OLcode As Long
Dim myCount As Integer
myCount = 0
EntOLCode14 = 188744602 ' Value for
pjTaskEnterpriseProjectOutlineCode14 "Task Enterprise Project
Outline
Code14"

Set entOutlineCodes = Application.GlobalOutlineCodes

For Each oc In entOutlineCodes
OLcode = oc.FieldID

Select Case OLcode
Case EntOLcode14 ' Proj Mgr
Set lt = oc.LookupTable
Debug.Print "--Start of Project Manager loop at " &
Now
&
"---"
For Each lte In lt
cboProjMgr.AddItem lte.FullName
Next
cboProjMgr.Value =
ActiveProject.ProjectSummaryTask.GetField(pjTaskEnterpriseProjectOutlineCode14)
txtProjMgr.Value = cboProjMgr.Value
... other case statements
End Select

Next
'----- code ends here -----

The For loop is taking a lot of time to execute.
Is there an elegant solution which will allow me to read just the
required
outline code without the loop?

I searched on Google Groups and found a similar answer posted by
Evgeniy
(http://groups.google.com/group/micr...ee0795bb7e3/23460d81b74e1d9a#23460d81b74e1d9a)
The code he mentioned was
'---- code starts here -----
Dim tsk As msproject.Task
MsgBox (tsk.GetField(pjTaskEnterpriseProjectOutlineCode14))
'---- code ends here ------

But this is not working for me. Its giving an error "Object variable
or
With block variable not set"

Also the MSDN documentation on this topic is very sparse.
Can anyone please help.

Thanks
Coolpran
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
Resource outline code vs. Enterprise Number 3
Grouping tasks using an Enterprise Outline Code in PjPro 0
Prompt For Replace in Selection And More... 0
VBA word change keys 0
Custom Project Outline Code no longer in Edit on PWA 0
WORD VBA Search and switch to advanced ... 1
PWA 2003 - modify fields in existing view. 5

Top