Macro to extract project specific resources

While working and extending a few macros recently, noticed i didn’t had access to database (same as in Project Online, well i could use Odata but again it cumbersome, so i went with my comfort zone VBA), and i needed to extract 15 project specific resources and thats where i started thinking should i do it manually, well easy enough if i only had 15 projects what if more than 100 and hence started writing macro, didn’t took longer may be 20 min and here is is

<code>

Sub GetAllRes()

‘Declare Object Variables for use

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

‘Set Excel Application, Workbook, Worksheet reference to be used
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = “AllRes”
Set xlRow = xlApp.ActiveCell

Dim allProj()

‘ Well i could have read this from a file as well, just got lazy as there were only handful projects 😉
allProj = Array(“Prj1”, “Prj2”, “Prj3”, “Prj4”, “Prj5”, “Prj6”, “Prj7”, “Prj8”, “Prj9”)
Dim CurrArr
Dim Res As Resource
Dim Row As Integer
Row = 1

xlSheet.Rows.Cells(0, 0) = “ProjectName”
xlSheet.Rows.Cells(0, 1) = “ResourceName”
xlSheet.Rows.Cells(0, 2) = “Resource Type”

For Each CurrArr In allProj
PrjName = “<>\” + CurrArr
FileOpenEx Name:=PrjName, ReadOnly:=True
For Each Res In ActiveProject.Resources
‘MsgBox (Res.Name + “–” + CStr(Res.EnterpriseUniqueID))
xlSheet.Rows.Cells(Row, 1) = CurrArr
xlSheet.Rows.Cells(Row, 2) = Res.Name
If Res.EnterpriseUniqueID = -1 Then
xlSheet.Rows.Cells(Row, 2) = “Local”
Else
xlSheet.Rows.Cells(Row, 2) = “Enterprise”
End If
Next

Next

End Sub

</code>

Let me know what you think

Advertisements

Read Full Post »

Reading Enterprise Custom Field Using Macro

Several Times I have seen this question coming up on how to read values of Enterprise Custom Fields, thought just inserting few lines here to aid out people looking for these, the below mentioned method will not only get you access to the Enterprise but out of box fields as well as local custom fields if any

<code>

Sub GetEntECFVal()

Dim tsks As Tasks
Dim tsk As Task

For Each tsk In ActiveProject.Tasks
MsgBox (tsk.Name + “—” + tsk.GetField(FieldNameToFieldConstant(“Field Name”)))
Next

End Sub

</code>

Read Full Post »