Data import from excel to project 1

[tonew]

Hi there,I was wondering if there is any way I could compare fields between excel and microsoft project and import values that matches the comparison.suppose I have two columns in excel named "Sky" and "River". I also have two fields in microsoft project named "sky" and "River". In my project file "Sky" column is already populated. Now I want to compare between project "Sky" column and excel "Sky" column. if they matches then my "river" field in project will be filled with data associated with excel river.I will really appreciate youir help!


Excel data
[pre][box]sky | river[/box][/pre]
[pre][box]blue | white[/box][/pre]
[pre][box]white | clear [/box] [/pre]
[pre][box]dark | black[/box][/pre]
[pre][box]black | shiny[/box] [/pre]

Project data
[pre][box]sky | river[/box][/pre]
[pre][box]blue | [/box] [/pre]
[pre][box]black | [/box] [/pre]
​

 

[JulieInMaine]

I don't believe so. You can test for values in a field in Project and display values in another field - but not as a lookup to Excel. Perhaps if you explained what you are trying to accomplish we could be of more help.

 

[tonew]

Hi Julie, Thanks for your reply.I am trying to move my all project information and schedules from excel to microsoft project.I have around 600 to 650 projects.But I need only 400 project information to move from excel.I already have my project file open with those 400 files , now I need to get lots of other information of those 400 projects from excel file to write to my existing project file, but looking one by one and copying them frpom excel to my project file is too much of work I realzed.This is why I needed to import values from excel.Could please give me some suggestion.Thanks a lot for your time.I really appreciate.

 

[SkipVought]

Hi,

It could probably be done via VBA code. How are your programming skills?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JulieInMaine]

If the values in the project files are unique (or you can create unique values in Excel) you may be able to merge additional data from the excel files into Project. However, if you set up the Excel files properly, you may be able to open the Excel file and import the data you need directly - without copying and pasting. Without understanding more of your Excel file configuration - it's hard to tell.

 

[tonew]

I had my unique identifier set for the excel file, and also tried to import from excel to project.Did not work also

 

[tonew]

Hi all, thanks for your feedback.@julie ,I had my unique identifier set for the excel file, and also tried to import from excel to project.Also my project had the same unique id.Did not work.I needed to do some lookup before I import that is the problem. @skipVought ,My programmimg skill is okay mainly c++ and assembly language, but I never did any vba code.Could you please give me some suuggestion.Thanks a lot.

 

[JulieInMaine]

Can you be more specific about what didn't work? Were you able to set up the map to map the data from Excel to Project?

 

[SkipVought]

alt+F11 will toggle between the application interface and VBA in any Microsoft application.

Check out faq707-4594.

Between the VBA Help in Project and faq707-4594, you ought to be able to discover what objects/properties will be useful to this effort, looking for some live example in your Project file. You will find that there are scores of various dates and other properties. It can make your head swim! But if you know that associated with some specigfic TASK is a specif value, for instance, you can find that TASK and find that VALUE and by association find the object/property that you need to manipulate.

Once you've discovered the specific object elements that you need to manipulate, the process of grabbing data in your Excel table and assigning it to a corresponding object property in Project, ought to be fairly simple.

Personally, what I know about Project could fill the bottom of a thimble. But I have coded processes between Excel and Project, using the advice given above to discover where it needed to go

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

FYI,

Here's the code, as an example, that updates several project files with data from and Excel table.

Option Explicit

Sub UpdateProjects()
    Dim iPR As Integer, pj As Object, r As Range, tsk As Object
    Dim sSHIP As String, sITEM As String, iPCT As Integer, ans
    
    ans = MsgBox("Are you ready?", vbYesNo)
    
    If ans = vbNo Then Exit Sub

'[b]OpenFiles opens 4 Project files[/b]
    OpenFiles

    Set pj = New MSProject.Application
    
    For iPR = 1 To 4
    
        '[b]process the 4 open Project files[/b]
        With pj.Projects(iPR)
            
            FilterInput ProjectName(.Name)

            For Each r In [tINPUT[Ship Number]].SpecialCells(xlCellTypeVisible)
                sSHIP = r.Value
                sITEM = Intersect(r.EntireRow, [tINPUT[item]])
                iPCT = Intersect(r.EntireRow, [tINPUT[Percent completed]])

                '[b]loop thru each Task for This Project[/b]
                For Each tsk In .Tasks
                    With tsk
                        
                        '[b]in This Task assign Text1, Text2 & PercentWorkComplete
                        '[highlight]I used the Watch Window to DISCOVER which Task Property I needed to manipulate[/highlight][/b]
                        If Format(.Text1, "000000") = sSHIP Then
                            If Format(.Text2, "00") = sITEM Then
                                .PercentWorkComplete = iPCT
                                Exit For
                            End If
                        End If
                    End With
                Next
            Next
        End With
    Next
    
    wsINPUT.ShowAllData
    
    Set pj = Nothing
End Sub
Sub test()
    MsgBox ProjectName("MIRABEL SHIP STATUS_206L_123456.mpp")
End Sub
Function ProjectName(FILE As String) As String
    Dim a, i As Integer
    
    a = Split(FILE, "_")
    
    For i = 0 To UBound(a) - 1
        ProjectName = ProjectName & a(i) & "_"
    Next
    ProjectName = Left(ProjectName, Len(ProjectName) - 1)
End Function

Sub FilterInput(CRIT As String)

'[b]wsInput is the Sheet Object for my Structured Table named tINPUT[/b]
    wsINPUT.ListObjects("tINPUT").Range.AutoFilter _
        Field:=[tINPUT[#headers]].Find("Project").Column, Criteria1:=CRIT
End Sub


Sub OpenFiles()
    Dim a, oFSO As Object, oFile As Object, i As Integer, sPath As String, sEXT As String
    Dim sSP, pj As Object
    
    Set pj = New MSProject.Application
    pj.Visible = True
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
'    sPath = "\\dfwsrv222\public\SkipM\Stephanie_MS_Project"
    sPath = "R:\Dept2b\MSTSCHED\Bennett, Stephanie"
    
    For Each oFile In oFSO.GetFolder(sPath).Files
        sEXT = Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))
        If sEXT Like "mpp*" Then
            pj.FileOpen oFile.Path
        End If
    Next
    
    Set pj = Nothing
    Set oFSO = Nothing
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonew]

Hi Julie ,Thanks for your reply.In my project file I went File->Open, selectrd my excel file,the import wizard popped and I hit "new map" and then "append the data to active project", next selectrd all options "Tasks,Res...." ,then I had my source worksheet name chosen, then as soon I type the excel field name , a window pops up saying "the field"" does not exist".But the name I typed is exactly same as my excel column.Thanks!

 

[tonew]

@SkipVought, Thanks so much for your reply.Links were really helpful.I will have to go through the code here.Hopefully I will get it.Thanks

 

[SkipVought]

EXACTLY what is the Field Name in question?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonew]

@SkipVought ,the field name is " Name " in excel,and in projectMy field name is "Name" and field title id "Name" too.Thanks.

 

[SkipVought]

Is there a SPACE leading and following " Name " in Excel as your post seems to suggest?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonew]

@SkipVought ,no there is no space in there,that is just a typo when I was typing here.Sorry for the confusion.Thanks.

 

[SkipVought]

...then I had my source worksheet name [highlight]chosen[/highlight], then as soon I [highlight]type[/highlight] the excel field...

Are you sure that there is not a place to [highlight]chose[/highlight] the field names, as you did with the sheet name?



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonew]

Yah, just double checked couple of times,no space at all.Thanks.

 

[SkipVought]

That was not related to the question I most recently asked.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonew]

Oh Sorry I got the question wrong, yes there is a dropdown box to choose the sheet name, by default it had the right sheet name chosen.But I could change from the list.sorry about that.