none
Excel export/import via VBA

    Pertanyaan

  • Hi all,

    are there any code samples for importing excel data to an existing project file?

    Just tried out the export/import wizard with merging on the unique id. For a simple export it is fine, but for import I need more options:

    After exporting tasks to an excel file, users will change values (name, Start10, Finish10 etc.), also the user have to decide, if changed tasks may go back to project or not. For this function I use a Flag field.

    So, I want to import these changed tasks from project and merge them to the existing tasks.

    Additional users should create new tasks in excel, and of course, these tasks should go also up to project.

    Thank you for help

    regards

    Peter




    17 Mei 2018 9:49

Jawaban

  • Peter,

    Here's a macro that runs in Project. As I noted earlier the exact code structure is highly dependent on the Excel Worksheet format so in order to run a test of this code I've included a screen shot of the Excel Worksheet format.

    Hope this helps.

    John

    'Macro written by John - Project
    'Version 1.0 9/25/15 11:00 AM
    Option Explicit
    Option Compare Text
    Public Const ver = " - 1.0"

    Public Xl As Excel.Application
    Public WB As Excel.Workbook
    Public s As Excel.Worksheet
    Public c As Excel.Range

    Public Tsks As Tasks
    Public UID As Single
    Public SeedDt As Date
    Public DurVal As Single, HPD As Single, HPW As Single, cf As Single
    Public numrows As Integer, i As Integer, p1 As Integer
    Public curcel As Variant    'could be either a number or text
    Sub ImportExcelDataToProject()
    MsgBox "This macro imports the following data fields from Excel:" & vbCr & _
        "    Task Name" & vbCr & "    Outline Level" & vbCr & _
        "    Duration" & vbCr & "    Start (if necessary)" & vbCr & _
        "    Predecessors" & vbCr & "    Resource Names" & vbCr & _
        "    Task Notes", vbInformation, "Import from Excel" & ver

    'Open the Excel workbook to gather data
    '   Note: Excel need not be running
    Set WB = Workbooks.Open(FileName:="C:\Users\John\Desktop\ExcelToProjectVBAImportX.xlsx")
    Set s = WB.Worksheets(1)
    'Create new Project file to receive imported data
    FileNew
    '----------------------
    'Gather some basic parameters from Excel and Project
    '   Find earliest start date used in Excel workbook
        sort1
    '   Find out how many rows of data in Excel worksheet
    '   (assumes first row is header, if there is none remove the "-1")
        numrows = WB.Worksheets(1).UsedRange.Rows.count - 1
    '   Find the default hours per day and hours per week settings for Project
        HPD = ActiveProject.HoursPerDay
        HPW = ActiveProject.HoursPerWeek
    '-----------------------
    'Read each row of data from the worksheet and create tasks in Project
    Application.Caption = "Progress"
    ActiveWindow.Caption = " Reading worksheet and exporting"
    Set c = s.Range("B2")   'set reference to first column of data to be imported
    Set Tsks = ActiveProject.Tasks
    For i = 0 To numrows - 1
        Tsks.Add.Name = c.offset(i, 0).Value
        'find the unique ID of the task just added
        '   since tasks are added in sequence, the count property identifies the current task
        '   (having the Unique ID facilitates expansion of the macro for increased functionality)
        UID = Tsks(Tsks.count).UniqueID
        
        Tsks.UniqueID(UID).OutlineLevel = c.offset(i, 1).Value
        'skip remaining columns for this row if this is destined to be a summary line in Project
        '   (Project calculates duration and start and best practices dictate no resources assigned)
        If c.offset(i, 2).Value <> "" Then
            'resolve units used in duration column of Excel worksheet
            DecodeXLDurUnits
            Tsks.UniqueID(UID).Duration = DurVal
            Tsks.UniqueID(UID).Predecessors = c.offset(i, 3).Value
            'if no predecessors exist for this task AND it starts after the Project Start Date
            '   then set start date. Note: this will set a start-no-earlier-than (SNET) constraint
            If Tsks.UniqueID(UID).Predecessors = "" And CStr(c.offset(i, 4).Value) > SeedDt Then
                Tsks.UniqueID(UID).Start = CStr(c.offset(i, 4).Value)
            End If
            Tsks.UniqueID(UID).ResourceNames = c.offset(i, 5).Value
        End If
        Tsks.UniqueID(UID).Notes = c.offset(i, 6).Value
    Next i
    '------------------------
    'Finally, close and exit
    MsgBox "Data Import is complete", vbOKOnly, "Import from Excel"
    Application.Caption = ""
    ActiveWindow.Caption = ""
    WB.Close savechanges:=False
    End Sub
    'This routine determines if duration column in Excel is in minutes, hours, days or weeks
    '   (most likely units) and then adjusts the data accordingly for import to Project
    Sub DecodeXLDurUnits()
        curcel = c.offset(i, 2).Value
        'default if duration column is in minutes
        p1 = Len(CStr(curcel)) + 1
        cf = 1
        If InStr(curcel, "h") > 0 Then
            p1 = InStr(curcel, "h")
            cf = 60
        ElseIf InStr(curcel, "d") > 0 Then
            p1 = InStr(curcel, "d")
            cf = HPD * 60
        ElseIf InStr(curcel, "w") > 0 Then
            p1 = InStr(curcel, "w")
            cf = HPW * 60
        End If
        'convert duration value to be in minutes for Project import
        DurVal = CSng(Mid(curcel, 1, p1 - 1)) * cf
    End Sub
    'This routine examines the pre-formatted Excel Workbook Start column and finds the
    '   earliest date. This is then used to set the Project Start Date
    Sub sort1()
    Dim cnt As Integer
    numrows = s.UsedRange.Rows.count
    SeedDt = "12/31/2049"     'maintain compatibility with Pre-Project 2013 versions
    Set c = s.Range("F2")
    For i = 0 To numrows - 1
        If c.offset(i, 0).Value <> "" And c.offset(i, 0).Value < SeedDt Then SeedDt = c.offset(i, 0).Value
    Next i
    ActiveProject.ProjectStart = SeedDt
    End Sub

    18 Mei 2018 13:49

Semua Balasan

  • Peter,

    Macro code to export Project data to Excel is pretty straightforward since Project has a very defined structure whereas Excel is more "free-form". Going the other way (Excel to Project) is a little more challenging since format of the Excel data is almost limitless whereas Project's structure is very defined.

    I do have some VBA code that exports Excel data to Project and I actually have two versions, one is based in Excel and the other is based in Project. Because of what I said above, both are totally dependent on the Excel worksheet format (i.e. what columns and what rows represent what data).

    Are you interested in running the code from Project or from Excel?

    What is your level of expertise with VBA?

    John

    17 Mei 2018 13:21
  • John,

    I know project very well and did already vba stuff within project. I also understand that project is the database and excel is a sheet ;)

    I am not shure what is the better way, project vba get excel data or excel vba putting to project, yet. I gonna proof both options, so I have the choice later to decide ...

    At the moment I do some tests doing it with project vba ... my vba level is good (project, excel, access)

    I like the way from project, because that will be the leading system.

    Peter

    17 Mei 2018 15:02
  • Peter,

    Okay, so do you want some guidance or do you want to try it on your own?

    John

    17 Mei 2018 15:47
  • John

    At the moment it's just a proofing of feasability and estimated effort for a customer.

    I think about this:

    1. MS Project file (one project) export ---> MS Excel file (one project), some tasks and summary tasks
      easy, just with the wizard or vba map edit
    2. MS Project file (one project) export ---> MS Excel file (many projects in one file)

    3. MS Excel file (one project) ---> MS Project file (one project)
      getting data from mpp or sending from xlsx to mpp
      getting already existing task information like start/finish etc., merge with existing tasks in mpp file
      getting new tasks (added in xlsx file) and add to summary tasks in  mpp file

    4. MS Excel file (many projects) --> MS Project (one project)
      scope like 3.

    major points:

    • only one project in mpp file
    • many projects in one excel file

    If I am shure this concept will work, I suppose I need some guidance. But at the moment I just need some sample code getting data from an existing excel sheet.

    I would be glad if I could get some sample vba code from you.

    Peter


    18 Mei 2018 6:34
  • Peter,

    Here's a macro that runs in Project. As I noted earlier the exact code structure is highly dependent on the Excel Worksheet format so in order to run a test of this code I've included a screen shot of the Excel Worksheet format.

    Hope this helps.

    John

    'Macro written by John - Project
    'Version 1.0 9/25/15 11:00 AM
    Option Explicit
    Option Compare Text
    Public Const ver = " - 1.0"

    Public Xl As Excel.Application
    Public WB As Excel.Workbook
    Public s As Excel.Worksheet
    Public c As Excel.Range

    Public Tsks As Tasks
    Public UID As Single
    Public SeedDt As Date
    Public DurVal As Single, HPD As Single, HPW As Single, cf As Single
    Public numrows As Integer, i As Integer, p1 As Integer
    Public curcel As Variant    'could be either a number or text
    Sub ImportExcelDataToProject()
    MsgBox "This macro imports the following data fields from Excel:" & vbCr & _
        "    Task Name" & vbCr & "    Outline Level" & vbCr & _
        "    Duration" & vbCr & "    Start (if necessary)" & vbCr & _
        "    Predecessors" & vbCr & "    Resource Names" & vbCr & _
        "    Task Notes", vbInformation, "Import from Excel" & ver

    'Open the Excel workbook to gather data
    '   Note: Excel need not be running
    Set WB = Workbooks.Open(FileName:="C:\Users\John\Desktop\ExcelToProjectVBAImportX.xlsx")
    Set s = WB.Worksheets(1)
    'Create new Project file to receive imported data
    FileNew
    '----------------------
    'Gather some basic parameters from Excel and Project
    '   Find earliest start date used in Excel workbook
        sort1
    '   Find out how many rows of data in Excel worksheet
    '   (assumes first row is header, if there is none remove the "-1")
        numrows = WB.Worksheets(1).UsedRange.Rows.count - 1
    '   Find the default hours per day and hours per week settings for Project
        HPD = ActiveProject.HoursPerDay
        HPW = ActiveProject.HoursPerWeek
    '-----------------------
    'Read each row of data from the worksheet and create tasks in Project
    Application.Caption = "Progress"
    ActiveWindow.Caption = " Reading worksheet and exporting"
    Set c = s.Range("B2")   'set reference to first column of data to be imported
    Set Tsks = ActiveProject.Tasks
    For i = 0 To numrows - 1
        Tsks.Add.Name = c.offset(i, 0).Value
        'find the unique ID of the task just added
        '   since tasks are added in sequence, the count property identifies the current task
        '   (having the Unique ID facilitates expansion of the macro for increased functionality)
        UID = Tsks(Tsks.count).UniqueID
        
        Tsks.UniqueID(UID).OutlineLevel = c.offset(i, 1).Value
        'skip remaining columns for this row if this is destined to be a summary line in Project
        '   (Project calculates duration and start and best practices dictate no resources assigned)
        If c.offset(i, 2).Value <> "" Then
            'resolve units used in duration column of Excel worksheet
            DecodeXLDurUnits
            Tsks.UniqueID(UID).Duration = DurVal
            Tsks.UniqueID(UID).Predecessors = c.offset(i, 3).Value
            'if no predecessors exist for this task AND it starts after the Project Start Date
            '   then set start date. Note: this will set a start-no-earlier-than (SNET) constraint
            If Tsks.UniqueID(UID).Predecessors = "" And CStr(c.offset(i, 4).Value) > SeedDt Then
                Tsks.UniqueID(UID).Start = CStr(c.offset(i, 4).Value)
            End If
            Tsks.UniqueID(UID).ResourceNames = c.offset(i, 5).Value
        End If
        Tsks.UniqueID(UID).Notes = c.offset(i, 6).Value
    Next i
    '------------------------
    'Finally, close and exit
    MsgBox "Data Import is complete", vbOKOnly, "Import from Excel"
    Application.Caption = ""
    ActiveWindow.Caption = ""
    WB.Close savechanges:=False
    End Sub
    'This routine determines if duration column in Excel is in minutes, hours, days or weeks
    '   (most likely units) and then adjusts the data accordingly for import to Project
    Sub DecodeXLDurUnits()
        curcel = c.offset(i, 2).Value
        'default if duration column is in minutes
        p1 = Len(CStr(curcel)) + 1
        cf = 1
        If InStr(curcel, "h") > 0 Then
            p1 = InStr(curcel, "h")
            cf = 60
        ElseIf InStr(curcel, "d") > 0 Then
            p1 = InStr(curcel, "d")
            cf = HPD * 60
        ElseIf InStr(curcel, "w") > 0 Then
            p1 = InStr(curcel, "w")
            cf = HPW * 60
        End If
        'convert duration value to be in minutes for Project import
        DurVal = CSng(Mid(curcel, 1, p1 - 1)) * cf
    End Sub
    'This routine examines the pre-formatted Excel Workbook Start column and finds the
    '   earliest date. This is then used to set the Project Start Date
    Sub sort1()
    Dim cnt As Integer
    numrows = s.UsedRange.Rows.count
    SeedDt = "12/31/2049"     'maintain compatibility with Pre-Project 2013 versions
    Set c = s.Range("F2")
    For i = 0 To numrows - 1
        If c.offset(i, 0).Value <> "" And c.offset(i, 0).Value < SeedDt Then SeedDt = c.offset(i, 0).Value
    Next i
    ActiveProject.ProjectStart = SeedDt
    End Sub

    18 Mei 2018 13:49
  • Hi John,

    this is exactly what I needed to start some action. I will do the setup on my system next week.

    In the meanwhile I started testing this import mapping stuff within ms project (map with own defined merge key), and it is running smoothly so far.

    To do the more difficult functions I need another method, so like you code .... and more.

    I will come back if I have done the necessary groundwork, maybe then I need some guiadance.

    Regards and a nice weekend

    Peter

    19 Mei 2018 5:36
  • Peter,

    You're welcome and thanks for the feedback. If I answered your question, please consider marking my response as the answer. Note, marking a response as the answer does not prevent further discussion.

    John

    19 Mei 2018 14:45