Help with VBA

[OTPM]

Hi Folks

I need help in modifying the following code to open an existing workbook and populate with data from a mpp file:

Sub ExportMasterScheduleData()
'Start Excel and create a new workbook
'Create column titles
'Export data and the project title
'Tidy up
Dim xlApp As Excel.Application
Dim xlRange As Excel.Range
Dim Dept As Task

'Start Excel and create a new workbook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add

'Create column titles
Set xlRange = xlApp.Range("A1")

xlRange.Range("C1") = "ID"
xlRange.Range("D1") = "Milestone"
xlRange.Range("E1") = "Summary"
xlRange.Range("F1") = "%Complete"
xlRange.Range("G1") = "Name"
xlRange.Range("H1") = "Duration"
xlRange.Range("I1") = "Start"
xlRange.Range("J1") = "Finish"
xlRange.Range("K1") = "Status"
xlRange.Range("L1") = "Predecessors"

With xlRange.Range("A1:N1")
.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With

'Export data and the project title
Set xlRange = xlRange.Range("A1")
For Each Dept In ActiveProject.Tasks
If Not Dept Is Nothing Then
With xlRange
.Range("C2") = Dept.ID
.Range("D2") = Dept.Milestone
.Range("E2") = Dept.Summary
.Range("F2") = Dept.PercentComplete
.Range("G2") = Dept.Name
.Range("H2") = Dept.Duration
.Range("I2") = Dept.Start
.Range("J2") = Dept.Finish
.Range("K2") = Dept.Status
'Add an asterisk to force Excel to treat the Predecessor as General format. ParseIT should remove it.
.Range("L2") = "*" & Dept.Predecessors
End With
End If
Set xlRange = xlRange.Offset(1, 0) 'Point to next row


Next Dept
'Tidy up
'xlRange.Range("A:L").EntireColumn.AutoFit
Set xlApp = Nothing
End Sub

Any help would be appreciated.

Thanks
Tony

[JulieS]

Hi Tony,

I'm afraid VBA is not among my skills. You might try posting to the MS forum for Microsoft programming:
http://social.technet.microsoft.com/...ct2010custprog

If you post to the MS forum, let them know what version of Project and Excel you are using. Rod Gill's book excerpts may also help.

See: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[OTPM]

Thanks Julie.

[JulieS]

Were others on the Programming forum able to point you in the right direction?

[OTPM]

Hi Julie
I posted it last night and am waiting for a response.
Thanks
Tony

[JulieS]

For those searching for information on this thread see:
http://social.technet.microsoft.com/...ct2010custprog