Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 40 of 40

Thread: Solved: Exporting from Excel to MS Project

  1. #21
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #22
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #23
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    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

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  4. #24
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.Count
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #25
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Bob - Nothing wrong with Rows.Count if we will be using 2 x CPUs and 8 GB RAM

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  6. #26
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    ROFL!!! Dennnis, you made my day. Thank you my friend. LOL!

  7. #27
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #28
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Bob,

    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
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  9. #29
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    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

  10. #30
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #31
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    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

  12. #32
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Xld,

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

    Dean

  13. #33
    VBAX Regular
    Joined
    May 2007
    Posts
    11
    Location
    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" & 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
    Last edited by Phatie17; 05-24-2007 at 08:35 AM.

  14. #34
    VBAX Regular
    Joined
    May 2007
    Posts
    11
    Location
    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" & 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

  15. #35
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can you post your workbook to save us the work re data.

  16. #36
    VBAX Regular
    Joined
    May 2007
    Posts
    11
    Location
    Here it is. Thanks for your help

  17. #37
    VBAX Newbie
    Joined
    Dec 2012
    Posts
    2
    Location

    Question

    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 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

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


    I once again want to thank you in advance for your help
    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

  18. #38
    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?

  19. #39
    interresting .......

  20. #40

    Push

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •