×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data import from excel to project

Data import from excel to project

Data import from excel to project

(OP)
[img ]
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
sky	   |  river 
blue	   |  white 
white      | clear    
dark  	   |  black 
black	   |  shiny  



Project data
sky	   |  river 
blue	   |         
black	   |         

RE: Data import from excel to project

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.

RE: Data import from excel to project

(OP)
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.

RE: Data import from excel to project

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

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.

RE: Data import from excel to project

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

RE: Data import from excel to project

(OP)
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.

RE: Data import from excel to project

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?

RE: Data import from excel to project

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

Check out FAQ707-4594: How to use the Watch Window as a Power Programming Tool.

Between the VBA Help in Project and FAQ707-4594: How to use the Watch Window as a Power Programming Tool, 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

FYI,

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

CODE

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

'OpenFiles opens 4 Project files
    OpenFiles

    Set pj = New MSProject.Application
    
    For iPR = 1 To 4
    
        'process the 4 open Project files
        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]])

                'loop thru each Task for This Project
                For Each tsk In .Tasks
                    With tsk
                        
                        'in This Task assign Text1, Text2 & PercentWorkComplete
                        'I used the Watch Window to DISCOVER which Task Property I needed to manipulate
                        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)

'wsInput is the Sheet Object for my Structured Table named tINPUT
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

(OP)
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!

RE: Data import from excel to project

(OP)
@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

RE: Data import from excel to project


EXACTLY what is the Field Name in question?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

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

RE: Data import from excel to project


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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

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

RE: Data import from excel to project


Quote:

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

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


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

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

RE: Data import from excel to project


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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

(OP)
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.

RE: Data import from excel to project


Quote (SkipVought)

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data import from excel to project

Project has had hiccups before when there are spaces in the Tab name in Excel or special characters. I would not try appending just try a straight import to see how much data you can capture. If you'd care to post a sample of the Excel file, we'll try to help.

RE: Data import from excel to project

(OP)
Hi Guys,Thanks a lot for your help.I was able to import with lookup from Excel to MSP.Really appreciate your help!

RE: Data import from excel to project

Great. Glad you were able to get things sorted.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close