none
Why does MS Project 2007 Import Wizard ignore my changes without prompting me? RRS feed

  • Question

  • I've given schedulers a simple Excel template to populate with data.  I've created and saved a map of these fields using MS Project's Import Wizard--it's merge with a key on a unique ID of my choosing.  When I perform the import (manually or with VBA) it:

    1.) Does not "clear" fields; if the scheduler has removed the data (Vendor Name) from a field associated with unique ID "12345", on import, "Widgets, Inc." remains in the MS project field though the field is blank in the Excel file.

    2.) One MS Project field is based on a look up table with a set of names.  "John Smith" and "Jane Doe" are in the list, and if the user changes the excel data (associated with 12345) from John Smith to Jane Doe, the update happens; however, if the user changes the excel data from John Smith to "jdoe" it does not update--which is good, except I want to be prompted that certain data did not get pushed.

    Ultimately, I want to call this import function via VBA (maybe through Excel or Access or MS Project) so I can just click a button rather than import each schedule update one at a time with the Import Wizard.  That said, I need to know when Project is accepting the data and when it's not.

    Monday, September 12, 2011 12:18 PM

All replies

  • Unfortunately you have hit some limitations of the import wizard. If you want null fields to delete the same field in project and provide logged errors when invalid names are entered, you'll need to write your own wizard in VBA.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Proposed as answer by Ismet Kocaman Friday, September 23, 2011 9:07 AM
    Tuesday, September 13, 2011 5:24 AM
    Moderator
  • Rod,

    Thanks for the response.  I do want to write my own wizard, but don't know how to go about it.  My recorded macro of the standard wizard doesn't tell me much:

    FileOpenEx Name:="C:\...\Schedule_Import.xls", ReadOnly:=False, Merge:=1,_ FormatID:="MSProject.XLS5", map:="map_name_here"

    Rather than using a pre-existing map, I recorded making a new one and tried running it by calling it through some Excel VBA that just opens MS Project and runs "create_new_map", but it failed on the first line:

     MapEdit Name:="Map 1", Create:=True, OverwriteExisting:=True, DataCategory:=0, _ CategoryEnabled:=True, TableName:="Task_Table1", FieldName:="CAM", _ ExternalFieldName:="CAM Name", ExportFilter:="All Tasks", ImportMethod:=2, _     HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), _                       TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False

    It says "The MergeKey argument is requried when merging has been specified as the import method".  So it knows I want to use the merge import method, but nowhere in the VBA does it show that I made that choice.

    Is there a simple way to write my own wizard, or do I really have to go through my excel file line-by-line, field-by-field?

    Wednesday, September 14, 2011 2:44 PM
  • Rod,

    Well, I managed to get a work around by replacing my data source's blank fields with "delete_txt", then running a replace "delete_txt" with "" in MS Project.  Crude, but it works.

    New problem:  have you ever come across bugs in the import wizard?  Specifically, I can set my field mappings and merge key and open my .xls file, and it will overwrite my merge key.

    For example:  my schedule has lines A-D (merge key 12345 --> 12348, not the unique ID in schedule, but unique to my datasource--I guarantee); I import lines E-H (with merge keys 4567 -->4570), and it will overwrite my 12345 --> 12348 lines, merge keys and all.

    I wrote this process in VBA, and thought I must have botched it...but I was able to replicate it using the wizard manually

    Now, it doesn't happen every time.  I've literally stepped through my code, and used the wizard by hand, and at the "FileOpenEx" event (post setting the field mappings / merge key) it merges correctly only half the time.

    Ever seen something like this ?

     

     

    Wednesday, November 16, 2011 5:44 PM
  • Hello tetsuo2030,

    In similar situation. Rather random effects from FileOpenEx; no consistency in results of the merge.

    So I am also on the brink of suspicion to 'bugs' in the function.

    Effects experienced:

    - updates not consistently transfered

    - task level messed up

     

    Thursday, January 19, 2012 7:39 PM
  • Do you have the latest Service Packs installed? If not, try them. Can you post the problem code?

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, January 19, 2012 8:12 PM
    Moderator
  • Rod,

    It's been a while.  Never found a solution concerning a merge key that wasn't MS Project's Unique ID; since it happened even when using the wizard, it doesn't sound code related.  But, either way, here's some code stolen straight from the macro recording of the wizard: 

    'call project Set appProj = CreateObject("MSProject.Application") 'open file, ignore the warnings appProj.FileOpen strPath, ReadOnly:=False, IgnoreReadOnlyRecommended:=True Set aProg = appProj.ActiveProject 'set security to LOW appProj.OptionsSecurityEx RemoveFileProperties:=False, TrustWSS:=False, LegacyFileFormats:=2 appProj.MapEdit Name:="Map 4", Create:=True, OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True, TableName:="Task_Table1", FieldName:="Unique ID", ExternalFieldName:="my_TempID", ExportFilter:="All Tasks", ImportMethod:=2, MergeKey:="Unique ID", HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Number14", ExternalFieldName:="mynumber14" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text16", ExternalFieldName:="WPM Name" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text4", ExternalFieldName:="myfield4" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text7", ExternalFieldName:="myfield7" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text1", ExternalFieldName:="myfield1" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text22", ExternalFieldName:="myfield22" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text3", ExternalFieldName:="myfield3" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text20", ExternalFieldName:="myfield20" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text19", ExternalFieldName:="myfield19" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text23", ExternalFieldName:="myfield23" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Name", ExternalFieldName:="myname" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Flag8", ExternalFieldName:="myflag8" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text2", ExternalFieldName:="myfield2" appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text24", ExternalFieldName:="myfield24" appProj.FileOpenEx Name:= path & "\Schedule_Import.xls", ReadOnly:=False, Merge:=1, FormatID:="MSProject.XLS8", map:="Map 4"

    My latest problem is this:  we installed SP3, and this code (that's worked all along which does use the Schedule Unique ID as the merge key) has stopped working.  I'm calling this code from Access, and the FileOpenEx (last line) isn't doing a thing.  One procedure later is an Append rather than a Merge, and that's not doing anything either.  My code still saves a schedule's tasks into excel just fine; it just doesn't accept my updates.

    This problem was brought to my attention by folks who just installed SP3; those who haven't installed aren't effected.

    andy

    Thursday, July 5, 2012 6:47 PM
  • Rod,

    We've Installed SP3, and I've tried everything I can think of, but the automated import wizard has stopped working.  It's a lot more robust than what's listed in the code, but this snippet sums it up; I'm attempting to open an excel file and merge it.

    1) Set appProj = CreateObject("MSProject.Application") to open an instance of Project.

    2.) appProj.FileOpen strPath, ReadOnly:=False, IgnoreReadOnlyRecommended:=True '(to open the excel file to be merged)

    Sample code:

    appProj.MapEdit Name:="Map 4", Create:=True, OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True, TableName:="Task_Table1", FieldName:="Unique ID", ExternalFieldName:="Schedule_TempID", ExportFilter:="All Tasks", ImportMethod:=2, MergeKey:="Unique ID", HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False
    appProj.MapEdit Name:="Map 4", DataCategory:=0, FieldName:="Text16", ExternalFieldName:="WPM Name"
    appProj.FileOpenEx Name:="C:\...\Schedule_Import.xls", ReadOnly:=False, Merge:=1, FormatID:="MSProject.XLS8", map:="Map 4"

    It works on a machine that doesn't have SP3 installed; SP3:  no joy.

    andy

    Wednesday, July 25, 2012 2:58 PM