PDA

View Full Version : Need Help Exporting dates from Excel into MS project



bcry
12-14-2012, 02:40 PM
Hello Folks,

I am a beginner to vb and am trying to do a similar task to this post done below by De,nis and would greatly appreciate your help.

I have extracted actual dates and forecasted dated across an excel row and am trying to populate it :banghead: into a ms project template where the task names are already populated.

Dim strCellA, strCellB As String
Dim iRow, iCol, k, k2, Col As Integer
Dim strResult(1 To 300) ' forecasted dated
Dim strResult2(1 To 300) 'Actualized dates

'Get Results-> Finish dates
strResult(1) = strCellA
k = 2
For iCol = 64 To 236 Step 3
If Not ((iCol = 187) Or (iCol = 196)) Then
strResult(k) = Cells(iRow, iCol)
k = k + 1
End If
Next iCol


'Get Results2->Actualized Dates
strResult2(1) = strCellB
k2 = 2
For iCol = 65 To 236 Step 3
If Not ((iCol = 188) Or (iCol = 197)) Then
strResult2(k2) = Cells(iRow, iCol)
k2 = k2 + 1
End If
Next iCol

Question: I think it needs some kind of loop to be entered into project fields :help

.Finish = strCellA-> this is not working
.ActualFinish = strCellB-> this is not working


I once again want to thank you in advance for your help: pray2:
Regards
Bcry


[quote=XL-Dennis]Hi Dean,

Below You find an example based on the information You so far has been given:


Option Explicit
Sub Add_Data_Project()
'Add a reference to Microsoft Project x.x Object Library
'in the VB-editor through the command Tools | References..
'The x.x stands for version like 11.0 or previously.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim stTeammember As String, stTask As String
Dim dtStartDate As Date
Dim lnTime As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
stTeammember = .Range("D5").Value
stTask = .Range("E5").Value
dtStartDate = .Range("F5").Value
lnTime = .Range("G5").Value
End With
'Instantiate and open MS Project and the project.
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Set prApp = New MSProject.Application
prApp.FileOpen "c:\VBAX.mpp"
Set prProject = prApp.ActiveProject
'Add task and other wanted information to the project.
With prProject
.Tasks.Add stTask
With .Tasks(stTask)
.Duration = lnTime & " days"
.ResourceNames = stTeammember
.Start = dtStartDate
End With
End With
'Save the project and close MS Project.
With prApp
.FileSave
.Quit
End With
MsgBox "The project VBAX has successfully been updated!", vbInformation
'Release objects from memory.
Set prProject = Nothing
Set prApp = Nothing
End Sub










Option Explicit Sub Add_Data_Project() 'Add a reference to Microsoft Project x.x Object Library 'in the VB-editor through the command Tools | References.. 'The x.x stands for version like 11.0 or previously. Dim wbBook As Workbook Dim wsSheet As Worksheet Dim stTeammember As String, stTask As String Dim dtStartDate As Date Dim lnTime As Long Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets(1) 'Populate the variables with values from the worksheet. With wsSheet stTeammember = .Range("D5").Value stTask = .Range("E5").Value dtStartDate = .Range("F5").Value lnTime = .Range("G5").Value End With 'Instantiate and open MS Project and the project. Dim prApp As MSProject.Application Dim prProject As MSProject.Project Set prApp = New MSProject.Application prApp.FileOpen "c:\VBAX.mpp" Set prProject = prApp.ActiveProject 'Add task and other wanted information to the project. With prProject .Tasks.Add stTask With .Tasks(stTask) .Duration = lnTime & " days" .ResourceNames = stTeammember .Start = dtStartDate End With End With 'Save the project and close MS Project. With prApp .FileSave .Quit End With MsgBox "The project VBAX has successfully been updated!", vbInformation 'Release objects from memory. Set prProject = Nothing Set prApp = Nothing End Sub