Exporting Project to excel using VBA Code

%3CLINGO-SUB%20id%3D%22lingo-sub-991025%22%20slang%3D%22en-US%22%3EExporting%20Project%20to%20excel%20using%20VBA%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-991025%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20help%20me%20I%20have%20to%20export%20my%20ms%20project%20file%20to%20excel%20but%20have%20tried%20the%20vba%20code%20from%20some%20users%20and%20there%20are%20bugs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20using%20Office%20365%20I%20looking%20for%20anyone%20who%20has%20working%20code%20for%20ms%20project%202016%20to%20convert%20to%20excel%3C%2FP%3E%3CP%3EThe%20code%20I'm%20currently%20using%20is%20but%20wont%20go%20past%20the%20line%20%22Dim%20xlApp%20As%20Excel.Application%3A%3C%2FP%3E%3CP%3E%3CEM%3ESub%20Macro1()%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E'%20Macro%20Macro1%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20TaskDep%20As%20TaskDependency%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20ts%20As%20Tasks%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20t%20As%20Task%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20y%20As%20Long%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20xlApp%20As%20Excel.Application%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20ws%20As%20Worksheet%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20wb%20As%20Workbook%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDim%20bTaskDepSucc%20As%20Boolean%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EConst%20consMinInDay%20As%20Long%20%3D%20480%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3ESet%20xlApp%20%3D%20CreateObject(%22Excel.Application%22)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ExlApp.Workbooks.Add%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ESet%20wb%20%3D%20xlApp.ActiveWorkbook%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ESet%20ws%20%3D%20wb.ActiveSheet%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ExlApp.Visible%20%3D%20True%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%201)%20%3D%20%22Task%20UID%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%202)%20%3D%20%22Task%20ID%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%203)%20%3D%20%22Task%20Name%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%204)%20%3D%20%22Task%20Pct%20Complete%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%205)%20%3D%20%22Task%20Succs%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%206)%20%3D%20%22Task%20Finish%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%207)%20%3D%20%22Succ%20UID%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%208)%3C%2Fimg%3E%20%3D%20%22Succ%20ID%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%209)%20%3D%20%22Succ%20Name%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%2010)%20%3D%20%22Succ%20Type%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%2011)%20%3D%20%22Succ%20Lag%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%2012)%20%3D%20%22Succ%20Pct%20Complete%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(1%2C%2013)%20%3D%20%22Succ%20Start%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ey%20%3D%202%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EWindowActivate%20TopPane%3A%3DTrue%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ESelectSheet%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ESet%20ts%20%3D%20ActiveSelection.Tasks%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EFor%20Each%20t%20In%20ts%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20(Not%20t%20Is%20Nothing)%20And%20(Not%20t.ExternalTask)%20And%20(Not%20t.Summary)%20_%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EAnd%20t.ActualFinish%20%3D%20%22NA%22%20And%20InStr(1%2C%20t.UniqueIDSuccessors%2C%20%22SS%22)%20%26gt%3B%200%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EbTaskDepSucc%20%3D%20False%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EFor%20Each%20TaskDep%20In%20t.TaskDependencies%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20t.UniqueID%20%3D%20TaskDep.From%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20TaskDep.Type%20%3D%203%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EbTaskDepSucc%20%3D%20True%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EElse%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EbTaskDepSucc%20%3D%20False%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EExit%20For%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ENext%20TaskDep%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20bTaskDepSucc%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EFor%20Each%20TaskDep%20In%20t.TaskDependencies%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20t.UniqueID%20%3D%20TaskDep.From%20Then%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%201)%20%3D%20t.UniqueID%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%202)%20%3D%20t.ID%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%203)%20%3D%20t.Name%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%204)%20%3D%20t.PercentComplete%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%205).NumberFormat%20%3D%20%22%40%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%205)%20%3D%20t.UniqueIDSuccessors%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%206)%20%3D%20Format(t.Finish%2C%20%22mm%2Fdd%2Fyyyy%22)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%207)%20%3D%20TaskDep.To.UniqueID%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%208)%3C%2Fimg%3E%20%3D%20TaskDep.To.ID%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%209)%20%3D%20TaskDep.To.Name%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%2011)%20%3D%20TaskDep.Lag%20%2F%20consMinInDay%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%2012)%20%3D%20TaskDep.To.PercentComplete%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ews.Cells(y%2C%2013)%20%3D%20Format(TaskDep.To.Start%2C%20%22mm%2Fdd%2Fyyyy%22)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Ey%20%3D%20y%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ENext%20TaskDep%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ENext%20t%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-991025%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EProject%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Can someone help me I have to export my ms project file to excel but have tried the vba code from some users and there are bugs.

 

We are using Office 365 I looking for anyone who has working code for ms project 2016 to convert to excel

The code I'm currently using is but wont go past the line "Dim xlApp As Excel.Application:

Sub Macro1()
' Macro Macro1
Dim TaskDep As TaskDependency
Dim ts As Tasks
Dim t As Task
Dim y As Long
Dim xlApp As Excel.Application
Dim ws As Worksheet
Dim wb As Workbook
Dim bTaskDepSucc As Boolean

Const consMinInDay As Long = 480

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
Set wb = xlApp.ActiveWorkbook
Set ws = wb.ActiveSheet
xlApp.Visible = True
ws.Cells(1, 1) = "Task UID"
ws.Cells(1, 2) = "Task ID"
ws.Cells(1, 3) = "Task Name"
ws.Cells(1, 4) = "Task Pct Complete"
ws.Cells(1, 5) = "Task Succs"
ws.Cells(1, 6) = "Task Finish"
ws.Cells(1, 7) = "Succ UID"
ws.Cells(1, 8) = "Succ ID"
ws.Cells(1, 9) = "Succ Name"
ws.Cells(1, 10) = "Succ Type"
ws.Cells(1, 11) = "Succ Lag"
ws.Cells(1, 12) = "Succ Pct Complete"
ws.Cells(1, 13) = "Succ Start"
y = 2

WindowActivate TopPane:=True
SelectSheet
Set ts = ActiveSelection.Tasks
For Each t In ts
If (Not t Is Nothing) And (Not t.ExternalTask) And (Not t.Summary) _
And t.ActualFinish = "NA" And InStr(1, t.UniqueIDSuccessors, "SS") > 0 Then
bTaskDepSucc = False
For Each TaskDep In t.TaskDependencies
If t.UniqueID = TaskDep.From Then
If TaskDep.Type = 3 Then
bTaskDepSucc = True
Else
bTaskDepSucc = False
Exit For
End If
End If
Next TaskDep
If bTaskDepSucc Then
For Each TaskDep In t.TaskDependencies
If t.UniqueID = TaskDep.From Then
ws.Cells(y, 1) = t.UniqueID
ws.Cells(y, 2) = t.ID
ws.Cells(y, 3) = t.Name
ws.Cells(y, 4) = t.PercentComplete
ws.Cells(y, 5).NumberFormat = "@"
ws.Cells(y, 5) = t.UniqueIDSuccessors
ws.Cells(y, 6) = Format(t.Finish, "mm/dd/yyyy")
ws.Cells(y, 7) = TaskDep.To.UniqueID
ws.Cells(y, 8) = TaskDep.To.ID
ws.Cells(y, 9) = TaskDep.To.Name
ws.Cells(y, 11) = TaskDep.Lag / consMinInDay
ws.Cells(y, 12) = TaskDep.To.PercentComplete
ws.Cells(y, 13) = Format(TaskDep.To.Start, "mm/dd/yyyy")
y = y
End If
Next TaskDep
End If
End If
Next t
End Sub

 

0 Replies