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