Solved: Exporting from Excel to MS Project

Hi.

Im a beginner to VBA in general and using it with Excel and this may seem a dumb question but here goes. :dunno

Is there a way that i can export data from a specific sheet within excel to MS Project? My excel workbook contains team information from which everybody is assigned tasks and they put a time on those tasks. What i want is to transfer the name of team member and the amount of time (calculated by start and end dates) worked into MS Project.

Can this be done? Any help or information would be greatly appreciated.

Thanks.
DcD

Dean,

Per se it does not exist any dumb questions only relevant but it may exist stupid answers ;)

From which software do You want to do it? From Excel or from Project?

Kind regards,
Dennis

Hi Dennis,
Thanks for your reply. I was looking to do it through excel. Currently i have a project set up where everybody enters their time details, if i can somehow export this into MS Project that would be great.
Any suggestions greatly appreciated.

Thanks,
Dean.

Hi Dean,

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

[vba]
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
[/vba]

It works well with my copy of MS Project 2003 Pro. I'll check in tomorrow (my local time) to take part of Your findings.

BTW, You may consider to check that all wanted data is available in the sheet before export it to the project.

Kind regards,
Dennis

Hi Dennis,

Thanks again for your reply. Sorry for delay in response.

When i run what you provided I get errors in the section
.Start = dtStartDate
It says "Application defined or object defined error"??

Im not really certain what the message is telling me. Either the .start isnt a part of the Project Application or this should be an object, but im not sure how to resolve.

Any suggestions would be greatly appreciated.

Thanks again for all your help
Dean

Hi Dennis,

Pls disregard last msg, as i tracked it down. It was my stupid mistake. Had chosen the incorrect WorkSheet.

Thanks for your help. Once i corrected my error all is resolved.

THANKYOU.

Hi Dennis,

One question. With what you have coded, how would you cater for more than one Team Member or Task etc? Help is greatly appreciated.

Thanks.
Dean.

Hi Dean,


Glad You managed to sort it out and below You find one example how to add several tasks, members etc to a project.

[vba]
Option Explicit
Sub Add_Several_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 vaTeammembers As Variant, vaTasks As Variant
Dim vaStartDates As Variant, vaTime As Variant
Dim lnStart As Long, lnCounter As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
lnStart = .Range("D65536").End(xlUp).Row
vaTeammembers = .Range("D5:D" & lnStart).Value
vaTasks = .Range("E5:E" & lnStart).Value
vaStartDates = .Range("F5:F" & lnStart).Value
vaTime = .Range("G5:G" & lnStart).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
For lnCounter = 1 To UBound(vaTasks)
.Tasks.Add vaTasks(lnCounter, 1)
With .Tasks(vaTasks(lnCounter, 1))
.Duration = vaTime(lnCounter, 1) & " days"
.ResourceNames = vaTeammembers(lnCounter, 1)
.Start = vaStartDates(lnCounter, 1)
End With
Next lnCounter
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
[/vba]

Kind regards,
Dennis

Worked perfectly.

Thanks very much...

You're welcome and I may some day make a write up about automation of MS Project from MS Excel.

Kind regards,
Dennis

That would be handy for me!! Definitely.

Once again thanks Dennis.

Dennis,

Sorry to bother you, but im having some troubles. It is doubling up the tasks in Project.

i know its because of this:

With prProject
For lnCounter = 1 To UBound(vaTasks)
.Tasks.Add vaTasks(lnCounter, 1) --> This section
With .Tasks(vaTasks(lnCounter, 1))
.Duration = vaTime(lnCounter, 1) & " days"
.ResourceNames = vaTeammembers(lnCounter, 1)
.Start = vaStartDates(lnCounter, 1)
End With
Next lnCounter
End With


I tried to do a condition that if the task existed in Project, then skip this add option, however i cant get it to find a match. Any assistance greatly appreciated...

Thanks.

Hi,


See if the following revised solutions is workable for You:

[vba]
Sub Add_Several_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 vaTeammembers As Variant, vaTasks As Variant
Dim vaStartDates As Variant, vaTime As Variant
Dim lnStart As Long, lnCounter As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
lnStart = .Range("D65536").End(xlUp).Row
vaTeammembers = .Range("D5:D" & lnStart).Value
vaTasks = .Range("E5:E" & lnStart).Value
vaStartDates = .Range("F5:F" & lnStart).Value
vaTime = .Range("G5:G" & lnStart).Value
End With
'Instantiate and open MS Project and the project.
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
Dim prTask As MSProject.Task
Dim prResource As MSProject.Resource
Dim bFlagTask As Boolean
Dim bFlagResource As Boolean
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
For lnCounter = 1 To UBound(vaTasks)
bFlagResource = False
'Loop through the collection of present resources and check if a resource
'exist or not.
For Each prResource In prProject.Resources
If prResource.Name = vaTeammembers(lnCounter, 1) Then bFlagResource = True
Next prResource
'Add the resource to the project and add a rate to it.
If bFlagResource = False Then
.Resources.Add vaTeammembers(lnCounter, 1)
With .Resources(vaTeammembers(lnCounter, 1))
.StandardRate = 100
.OvertimeRate = 100 * 1.5
End With
End If
bFlagTask = False
'Loop through the collection of present tasks and check if a task
'exist or not.
For Each prTask In prProject.Tasks
If prTask.Name = vaTasks(lnCounter, 1) Then bFlagTask = True
Next prTask
If bFlagTask = False Then
.Tasks.Add vaTasks(lnCounter, 1)
With .Tasks(vaTasks(lnCounter, 1))
.Duration = vaTime(lnCounter, 1) & " days"
.ResourceNames = vaTeammembers(lnCounter, 1)
.Start = vaStartDates(lnCounter, 1)
End With
End If
Next lnCounter
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 prResource = Nothing: Set prTask = Nothing
Set prProject = Nothing: Set prApp = Nothing
End Sub
[/vba]

Kind regards,
Dennis

Thanks heaps.

It works perfectly. Thankyou!

Dean,

Thanks for the feedback and You're welcome. I've made a note that I will make a write up about automating MS Project from Excel and transfer data.

Kind regards.
Dennis

Hi Dennis.

Ive run into a problem. When others use excel workbook and they dont have ms project it gives them debug errors. Is there a way where i can disable the MS Project reference for users that do not have ms project?

Ive tried but with no luck what so ever.

Thanks

Not tested, but the principle seems sound

[VBA]Option Explicit

Sub Add_Several_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 vaTeammembers As Variant, vaTasks As Variant
Dim vaStartDates As Variant, vaTime As Variant
Dim lnStart As Long, lnCounter As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Populate the variables with values from the worksheet.
With wsSheet
lnStart = .Range("D65536").End(xlUp).Row
vaTeammembers = .Range("D5:D" & lnStart).Value
vaTasks = .Range("E5:E" & lnStart).Value
vaStartDates = .Range("F5:F" & lnStart).Value
vaTime = .Range("G5:G" & lnStart).Value
End With
'Instantiate and open MS Project and the project.
Dim prApp As Object 'MSProject.Application
Dim prProject As Object 'MSProject.Project
Dim prTask As Object 'MSProject.Task
Dim prResource As Object 'MSProject.Resource
Dim bFlagTask As Boolean
Dim bFlagResource As Boolean
On Error Resume Next
Set prApp = CreateObject("MSProject.Application")
On Error GoTo 0
If Not prApp Is Nothing Then
prApp.FileOpen "c:\VBAX.mpp"
Set prProject = prApp.ActiveProject
'Add task and other wanted information to the project.
With prProject
For lnCounter = 1 To UBound(vaTasks)
bFlagResource = False
'Loop through the collection of present resources and check if a resource
'exist or not.
For Each prResource In prProject.Resources
If prResource.Name = vaTeammembers(lnCounter, 1) Then bFlagResource = True
Next prResource
'Add the resource to the project and add a rate to it.
If bFlagResource = False Then
.Resources.Add vaTeammembers(lnCounter, 1)
With .Resources(vaTeammembers(lnCounter, 1))
.StandardRate = 100
.OvertimeRate = 100 * 1.5
End With
End If
bFlagTask = False
'Loop through the collection of present tasks and check if a task
'exist or not.
For Each prTask In prProject.Tasks
If prTask.Name = vaTasks(lnCounter, 1) Then bFlagTask = True
Next prTask
If bFlagTask = False Then
.Tasks.Add vaTasks(lnCounter, 1)
With .Tasks(vaTasks(lnCounter, 1))
.Duration = vaTime(lnCounter, 1) & " days"
.ResourceNames = vaTeammembers(lnCounter, 1)
.Start = vaStartDates(lnCounter, 1)
End With
End If
Next lnCounter
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 prResource = Nothing: Set prTask = Nothing
Set prProject = Nothing: Set prApp = Nothing
End If
End Sub
[/VBA]

I agree with Bob and just to clarify:

If You at present have set a reference to the external library of MS Project in You workbook then remove it as the revised sample is based on late binding which make it possible to evaluate if MS Project exist or not on the running machine.

Kind regards,
Dennis

Sorry. I should clarify.

Basically i created this 'tool' to track team members, with the assistance of yourself. Each team member opens excel and fills in what they have done. All members have there own machines, except some dont have MS Project, with no need of it.

I have ms project, and the reference is in excel. When they open it, they dont have ms project and in there vba references there is a "MISSING : Ms Project 11.0 Object Library". The problem happens with other code in different sheets when the MISSING reference is ticked it doesnt recognise Chr. Untick the missing reference library fixes the problem. They dont need the "Add to Project" Command as it is only for team leaders.

Could you create something like a button or checkbox that when selected will turn on/off that reference? Is this possible?

Thanks heaps.

[vba]lnStart = .Range("D65536").End(xlUp).Row[/vba]

.. hmm, xld, are you digressing from your own standings on obtaining the last row?? ;)

Quote:

---

Originally Posted by DcD

Sorry. I should clarify.

Basically i created this 'tool' to track team members, with the assistance of yourself. Each team member opens excel and fills in what they have done. All members have there own machines, except some dont have MS Project, with no need of it.

I have ms project, and the reference is in excel. When they open it, they dont have ms project and in there vba references there is a "MISSING : Ms Project 11.0 Object Library". The problem happens with other code in different sheets when the MISSING reference is ticked it doesnt recognise Chr. Untick the missing reference library fixes the problem. They dont need the "Add to Project" Command as it is only for team leaders.

Could you create something like a button or checkbox that when selected will turn on/off that reference? Is this possible?

---

Dean,

The code as I gave you doesn't need that reference set. If you uncheck it as Dennis said, then distribute it you should get no problems.

Quote:

---

Originally Posted by firefytr

[vba]lnStart = .Range("D65536").End(xlUp).Row[/vba]

.. hmm, xld, are you digressing from your own standings on obtaining the last row?? ;)

---

Semi-guilty!

That part of the code was not mine, I just added the code to overcome the problem. I should change it though I agree http://vbaexpress.com/forum/images/smilies/doh.gif

Dean - Use the so called "late binding"

Bob - Next time change it as Zack seems to have more sparetime then the rest of the world ;)

Zack - Get a life :beerchug:

Kind regards,
Dennis

Quote:

---

Originally Posted by XL-Dennis

Bob - Next time change it as Zack seems to have more sparetime then the rest of the world ;)

---

Dennis,

Now that Excel 12 will have 1M+ rows, I might go on a new evangelising crusade to persuade people not to use them all, using 65536 rows for my end-point rather than Rows.Counthttp://vbaexpress.com/forum/images/smilies/devil.gif

Bob - Nothing wrong with Rows.Count if we will be using 2 x CPUs and 8 GB RAM :)

Kind regards,
Dennis

ROFL!!! Dennnis, you made my day. Thank you my friend. LOL! :D

Quote:

---

Originally Posted by XL-Dennis

Bob - Nothing wrong with Rows.Count if we will be using 2 x CPUs and 8 GB RAM

---

Dennis,

Apart from the fact that I only have 1 CPU and 512Mb RAM (which will struggle with Vista as I understand it), my crusade is aimed at not using Excel when one should be using a database. Too many people do it already, and with more than 1M rows, it will be even worse. Still, lots of work for the consultants.

Bob,

Quote:

---

my crusade is aimed at not using Excel when one should be using a database.

---

Then we are at least two with that opinion.

During the years I have seen so many poor solutions due to the overload in workbooks.

I'm also supporting and recommend to use simple textfiles to store data in.

After all, Excel is still a spreadsheet and not a DBMS ;)

As for Vista it will raise the requirements a lot on RAM and graphic card as well.

Kind regards,
Dennis

Hi Xld & Everybody.

Thanks for all the information. The code that was provided from Xld that allowed me to deselect the reference for microsoft project doesnt allow me to update the MS Project with Changes. Where as the old code from Dennis did. It doesnt seem to save any changes to existing data now.

Is there something that im missing?

Thanks.
Dean

Quote:

---

Originally Posted by DcD

Hi Xld & Everybody.

Thanks for all the information. The code that was provided from Xld that allowed me to deselect the reference for microsoft project doesnt allow me to update the MS Project with Changes. Where as the old code from Dennis did. It doesnt seem to save any changes to existing data now.

---

Dean,

Can you post a zip file with the xls amd MS Project files to help us test it?

Hi Xld,

Here is the xls and the mpp file as requested. Hope this helps.

Excuse the mess, as im learning...

Thanks once again.

Dean

Hi Xld,

Has there been any luck. Ive tried changing some things, but to no avail. Hopefully you've had more luck

Hey I have a similar problem.

I have two tabs, one is Refine and Add Factors and the other is Inventory. Im am running a nested loop and when I get to the second interation of the outter loop a problem occurs. The task is being added but the outline level and work associated with that task is being kicked back up to the top and rewriting the values associated to the other tasks.

Here is the code:

Private Sub cmdGenerate_Click()
'Variables surrounding applications
Dim wbEstimates As Workbook
Dim wsRefineAddFactors As Worksheet
Dim wsInventory As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
'Variables surrounding Refine or Add Factors Tab
Dim vaFactorNames As Variant
Dim vaResources As Variant
Dim vaTasks As Variant
Dim vaOutlineLevel As Variant
Dim vaPhase As Variant
Dim vaWBS As Variant
Dim lnEnd As Long, lnRefineAddCounter As Long
'Variables surrounding Inventory Tab
Dim vaFactors As Variant
Dim vaRequirements As Variant
Dim vaAnalysisDesign As Variant
Dim vaBuildUnitTest As Variant
Dim lnLast As Long, lnInventoryCounter As Long

Dim vaID As Variant
vaID = 1
Set wbEstimates = ThisWorkbook
Set wsInventory = wbEstimates.Worksheets(2)
'The parenthesis is the index of what worksheet needs to grabbed
Set wsRefineAddFactors = wbEstimates.Worksheets(1)
'This section is storing all the values on the Refine Add Factors Tab from the fields to
'the corresponding variables
With wsRefineAddFactors
lnEnd = .Range("C65536").End(xlUp).Row
vaResources = .Range("V15:V" & lnEnd).Value
vaTasks = .Range("J15:J" & lnEnd).Value
vaOutlineLevel = .Range("I15:I" & lnEnd).Value
vaFactorNames = .Range("C15:C" & lnEnd).Value
vaPhase = .Range("G15:G" & lnEnd).Value
vaWBS = .Range("H15:H" & lnEnd).Value
End With
'This section is storing all the values on the Inventory Tab from the fields to
'the corresponding variables
With wsInventory
lnLast = .Range("D65536").End(xlUp).Row
vaRequirements = .Range("R3:R" & lnLast).Value
vaAnalysisDesign = .Range("S3:S" & lnLast).Value
vaBuildUnitTest = .Range("T3:T" & lnLast).Value
vaFactors = .Range("D3:D" & lnLast).Value

End With

'Instantiate and open MS Project and the project.

Set prApp = New MSProject.Application
prApp.FileOpen "D:\Documents and Settings\adrian.martinez\Desktop\ACN\Wells Fargo\Estimating\CORE PMO Estimator\Work Plan Generation\New Utility\Workplan Generation 20070523 v 1 0.mpp"
Set prProject = prApp.ActiveProject

With prProject
For lnInventoryCounter = 1 To UBound(vaFactors)
For lnRefineAddCounter = 1 To UBound(vaTasks)


If vaFactors(lnInventoryCounter, 1) = vaFactorNames(lnRefineAddCounter, 1) Then
.Tasks.Add vaTasks(lnRefineAddCounter, 1)

With .Tasks(vaTasks(lnRefineAddCounter, 1)) ''''''''I know the problem is happening right here because the counter is going back to 1

.ResourceNames = vaResources(lnRefineAddCounter, 1)
.OutlineLevel = vaOutlineLevel(lnRefineAddCounter, 1)

Select Case vaFactorNames(lnRefineAddCounter, 1) & vaPhase(lnRefineAddCounter, 1) & vaWBS(lnRefineAddCounter, 1)

Case vaFactors(lnInventoryCounter, 1) & "Requirements" & "Task1"

.Work = vaRequirements(lnInventoryCounter, 1) * 60


Case vaFactors(lnInventoryCounter, 1) & "Analysis and Design" & "Task1"


.Work = vaAnalysisDesign(lnInventoryCounter, 1) * 60


Case vaFactors(lnInventoryCounter, 1) & "Build and Unit Test" & "Task1"


.Work = vaBuildUnitTest(lnInventoryCounter, 1) * 60

Case Else

End Select


End With

End If

Next lnRefineAddCounter
Next lnInventoryCounter
End With

End Sub

Im sorry here is the correct code



Private Sub cmdGenerate_Click()
'Variables surrounding applications
Dim wbEstimates As Workbook
Dim wsRefineAddFactors As Worksheet
Dim wsInventory As Worksheet
Dim prApp As MSProject.Application
Dim prProject As MSProject.Project
'Variables surrounding Refine or Add Factors Tab
Dim vaFactorNames As Variant
Dim vaResources As Variant
Dim vaTasks As Variant
Dim vaOutlineLevel As Variant
Dim vaPhase As Variant
Dim vaWBS As Variant
Dim lnEnd As Long, lnRefineAddCounter As Long
'Variables surrounding Inventory Tab
Dim vaFactors As Variant
Dim vaRequirements As Variant
Dim vaAnalysisDesign As Variant
Dim vaBuildUnitTest As Variant
Dim lnLast As Long, lnInventoryCounter As Long

Dim vaID As Variant
vaID = 1
Set wbEstimates = ThisWorkbook
Set wsInventory = wbEstimates.Worksheets(2)
'The parenthesis is the index of what worksheet needs to grabbed
Set wsRefineAddFactors = wbEstimates.Worksheets(1)
'This section is storing all the values on the Refine Add Factors Tab from the fields to
'the corresponding variables
With wsRefineAddFactors
lnEnd = .Range("C65536").End(xlUp).Row
vaResources = .Range("V15:V" & lnEnd).Value
vaTasks = .Range("J15:J" & lnEnd).Value
vaOutlineLevel = .Range("I15:I" & lnEnd).Value
vaFactorNames = .Range("C15:C" & lnEnd).Value
vaPhase = .Range("G15:G" & lnEnd).Value
vaWBS = .Range("H15:H" & lnEnd).Value
End With
'This section is storing all the values on the Inventory Tab from the fields to
'the corresponding variables
With wsInventory
lnLast = .Range("D65536").End(xlUp).Row
vaRequirements = .Range("R3:R" & lnLast).Value
vaAnalysisDesign = .Range("S3:S" & lnLast).Value
vaBuildUnitTest = .Range("T3:T" & lnLast).Value
vaFactors = .Range("D3:D" & lnLast).Value

End With

'Instantiate and open MS Project and the project.

Set prApp = New MSProject.Application
prApp.FileOpen "D:\Documents and Settings\adrian.martinez\Desktop\ACN\Wells Fargo\Estimating\CORE PMO Estimator\Work Plan Generation\New Utility\Workplan Generation 20070523 v 1 0.mpp"
Set prProject = prApp.ActiveProject

With prProject
For lnInventoryCounter = 1 To UBound(vaFactors)
For lnRefineAddCounter = 1 To UBound(vaTasks)


If vaFactors(lnInventoryCounter, 1) = vaFactorNames(lnRefineAddCounter, 1) Then
.Tasks.Add vaTasks(lnRefineAddCounter, 1)

With .Tasks(vaTasks(lnRefineAddCounter, 1)) ''''''''I know the problem is happening right here because the counter is going back to 1

.ResourceNames = vaResources(lnRefineAddCounter, 1)
.OutlineLevel = vaOutlineLevel(lnRefineAddCounter, 1)

Select Case vaFactorNames(lnRefineAddCounter, 1) & vaPhase(lnRefineAddCounter, 1) & vaWBS(lnRefineAddCounter, 1)

Case vaFactors(lnInventoryCounter, 1) & "Requirements" & "Task1"

.Work = vaRequirements(lnInventoryCounter, 1) * 60


Case vaFactors(lnInventoryCounter, 1) & "Analysis and Design" & "Task1"


.Work = vaAnalysisDesign(lnInventoryCounter, 1) * 60


Case vaFactors(lnInventoryCounter, 1) & "Build and Unit Test" & "Task1"


.Work = vaBuildUnitTest(lnInventoryCounter, 1) * 60

Case Else

End Select


End With

End If

Next lnRefineAddCounter
Next lnInventoryCounter
End With

End Sub

Can you post your workbook to save us the work re data.

Here it is. Thanks for your help

Hello Folks,

I am a beginner to vb and am trying to do a similar task to this post 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:

---

Originally Posted by XL-Dennis

Hi Dean,

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

[vba]
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
[/vba]

It works well with my copy of MS Project 2003 Pro. I'll check in tomorrow (my local time) to take part of Your findings.

BTW, You may consider to check that all wanted data is available in the sheet before export it to the project.

Kind regards,
Dennis

---

hello,
i tried this macro but it gives me problem with:
.Duration = lnTime & " days"

it seems that it dosent like & "days".
in Excel i have a number that defines the duration.
any help to solve this problem?

interresting .......

Hey guys,I just stumpled upon this very helpful thread with code that almost perfectly solves my problem - but somehow the code doesn't work out properly, as I get the "type mismatch" error as a result of the line .Duration = vaDuration(lnCounter, 1) & " days"I would really appreciate every piece of advice as to where this might come from.Thanks a lot,Chris