none
Project 2007 Importing a XLS into Project RRS feed

  • Question

  • I inhereted this macros and the team wants to change it...

    I am trying to open an excel document and assign the items to the project it was working fine for a while and now as i am testing it i get the error below.  The xls document is not open.

    dialogResult = FileOpenEx(Sheet:="STIM Import", FormatID:="MSProject.xls5.9", Map:="STIM ", DoNotLoadFromEnterprise:=True)

    First of all can some explain the Map:= parameter -- my problem may be in this parameter as I dont really know what the Map is refering to...

    I keep getting the following error -2147417848 : Method FileOpenEx of object =MSProject' failed : Error on line 1150 of cours the line about is 1150


    Kathy Tullis

    Saturday, April 5, 2014 1:03 AM

Answers

  • Kathy,

    Well yes that does provide more information but there are still questions. But before I get into those, let me ask a couple questions I should have asked in the beginning.

    In your initial post you said that the whole process was working fine but now it doesn't. Did you add any new software (applications, utilities, etc.) between the time it was working fine and now? If you did install something on your system, try removing that and see if it works.

    Even if you did not install any new software, did you do anything different with your Project template or Excel files? If so, what?

    You said you are using Project 2007. Is it updated with SP3? I assume you are also using Office 2007. Is it also updated to SP3? If both are not updated to that service pack, install them and see if that helps.

    If you did not install new new software, you did not change your Project or Excel files (other than normal editing) and Project and Office are fully up-to-date with respect to service packs, then unless someone else jumps in and says "I see what is wrong", I think we're going to have to take this to a more one-on-one level. In order to do that I need to actually see your Project and Excel files, (or at least a mockup of each hat demonstrates the problem), along with all the macros. Would you be willing/able to send me that data?

    John

    • Marked as answer by Kathy Tullis Monday, April 7, 2014 9:55 PM
    Monday, April 7, 2014 9:48 PM
  • John,

    I really appreciate all of your help.  I was recently upgraded to Windows 7 and while the PRF Version 7 had been working I did alot of changes to the xls file and had to make changes to PWA custom fields.

    Once Project Pro started shutting down each time I ran the macro I searched and found it said to do the service packs.  Of course we are supposed to get that automatically when we were upgraded.  However, when I looked I did have SP3 but not the April 2013 CU.  I applied that and guess what it worked PERFECTLY.  All of it.

    Thank you again for all your help.  I truely appreciate you taking the time.

    Kathy


    Kathy Tullis

    • Marked as answer by Kathy Tullis Monday, April 7, 2014 9:55 PM
    Monday, April 7, 2014 9:55 PM

All replies

  • Kathy,

    The map argument is an import map, typically created to map Excel Worksheet columns to Project fields. If the map doesn't exist then you will get an error. From Project go to Developer/manage group and hit the Organizer icon. Now select the map tab. Do you see STIM listed on either side? If not, then the map doesn't exist in either your Global or the current active Project and you will need to re-create it by opening the Excel file and going through the Project Import Wizard. If the map does exist then that's probably not the issue.

    I notice something possibly amiss about FormatID argument, it lists an Excel file format for Excel 97-2003. Are you trying to open a legacy Excel file? If the Excel file you are trying to import is from a current version of Excel (e.g. Excel 2010), then you should change that argument to: FormatID:="MSProject.xls".

    Finally, I don't quite follow your last paragraph. In part it says "Error on line 1150 of cours the line about is 1150". That doesn't quite make sense. Please clarify.

    John

    Saturday, April 5, 2014 2:58 AM
  • John,

    Thank you so much for explaining about the map.  I did indeed find the map.  The only thing that is mapped however is just the name.  All the rest is automated to append the data to the custom field attribute.

    I do realize we are asking for the "old" file format.  We had many people who did not have 2010 office and only had the xls format.  This is something that should be changed as well.  We do have the following code in the macro:

    Application.OptionsSecurityEx LegacyFileFormats:=PjLegacyFileFormats.pjEnableLegacyFilesLoad

    Sorry about the clarity issue on the final paragraph.  It tells me the error occurs on line 1150 and that is this line:

    dialogResult = FileOpenEx(Sheet:="STIM Import", FormatID:="MSProject.xls5.9", Map:="STIM ", DoNotLoadFromEnterprise:=True)

    Something about this is causing the macros to throw the  -2147417848 :  Error.  It does actually load all of the contents of the sheet into the project.  I then comment out the first part and run the remainder of the code and it runs fine and adds in all the correct project fields custom & delivered.

    Thanks you so much for your help on this.



    Kathy Tullis

    Saturday, April 5, 2014 6:26 PM
  • Kathy,

    I'm still a little confused. Apparently you're saying that the import map only imports the Name field but I don't quite understand what you mean by, "All the rest is automated to append the data to the custom field attribute". What exactly is that automated process? And what exactly do you mean by "custom field attribute"?

    When I mentioned the older versions of Excel I should have said that if you are importing any Excel version 2007 or later, you should use, FormatID:=MSProject.xls. It doesn't have to be Excel 2010.

    I'm still confused on the line 1150 thing. VBA code doesn't have line numbers so if the error message is giving you a line number, that line number is most likely the row in Excel where the data error is occurring. The "dialogRusult = ..." may be highlighted in code but that just indicates that import error is occurring while executing that statement.

    Then I'm even more confused by your next to last paragraph. It sounds like it runs fine but then it doesn't run fine. What exactly are you "commenting out"?

    John

    Sunday, April 6, 2014 2:41 AM
  • John,

    We require that they use the .xls format.  No xls* formats are allowed.

    Here is the code that we use to upload an excel spreadsheet...as you can see the person who originally created this included line numbers in the code.  I previously was commenting out everything before CallPopulatedCustomFields once I got all the 60 lines loaded.

    So I would run the PRF_Import_8_0 until the 1150 dialogResult and then it would blow up but the 60 lines were in the project plan...I would run it again but comment out most everything line 1200 and then run it and that part worked.   Now even running the PRF_Import_8_0 results in the application shutting down with no error message whatsoever.

    Public Sub PRF_Import_8_0()
    
              Dim projApp As Application
              Dim currentProj As Project
              Dim projTasks As Tasks
    
              'Internal Variables
              Dim preImportNumOfTasks As Integer
              Dim tempProjName As String
              Dim dialogResult As Boolean
              Dim savedTaskLinks As Collection
              Dim isSavedTaskLinks As Boolean
              Dim isTemplateDesired As VbMsgBoxResult
              Dim desiredTemplateName As String
    
              'Debug Variables
              Dim skipSave As Boolean
    50        skipSave = False
              Dim offlineTest As Boolean
    100       offlineTest = False
              Dim templateBypass As Boolean
    150       templateBypass = False
    
              'Set up the OM refs
    200       Set projApp = GetObject(, "MSProject.Application")
    
    250       On Error GoTo ErrorHandler
    '
    '300       RunProjectChecks
    '
    '350       If Not offlineTest Then CheckIfConnected
    
              'Changes Security Setting to allow legacy files
    400       Application.OptionsSecurityEx LegacyFileFormats:=PjLegacyFileFormats.pjEnableLegacyFilesLoad
    
    450       isTemplateDesired = False
    
    500       projApp.OpenUndoTransaction ("PRF Import")
    550           If projApp.Projects.Count > 0 Then
    600               Set currentProj = projApp.ActiveProject
    650               Set projTasks = currentProj.Tasks
    
    700               preImportNumOfTasks = projTasks.Count
    
    750               If preImportNumOfTasks > 0 Then
    800                   Set savedTaskLinks = SaveTaskLinks(currentProj)
    850                   isSavedTaskLinks = True
    900               End If
    
    950               dialogResult = FileOpenEx(Sheet:="STIM Import", ReadOnly:=True, FormatID:="MSProject.xls5.9", Map:="STIM - CR1 Mapping", DoNotLoadFromEnterprise:=True)
    
    1000              If dialogResult = False Then GoTo ExitSub
    1050          Else
    1150              dialogResult = FileOpenEx(Sheet:="STIM Import", ReadOnly:=True, FormatID:="MSProject.xls5.9", Map:="STIM - CR1 Mapping", DoNotLoadFromEnterprise:=True)
    
    1175              If dialogResult = False Then GoTo ExitSub
    
    1200              preImportNumOfTasks = 0
    1250              Set currentProj = projApp.ActiveProject
    1300              Set projTasks = currentProj.Tasks
                      
    1350          End If
    
                  'Populate the custom fields
    1400          Call PopulateCustomFields_8_0(currentProj, preImportNumOfTasks)
    
                  'Get the project name
    1450          If Not currentProj.Tasks(preImportNumOfTasks + 1).Name = "" Then
    1500              tempProjName = currentProj.Tasks(preImportNumOfTasks + 1).Name
    1550          Else
    1600              MsgBox MSG_PRF_MUST_HAVE_PROJECT_NAME, vbCritical + R_TO_L, Title:=Application.Name
    1650              GoTo ExitSub
    1700          End If
    
                  'Clear out the tasks we imported
    1750          Call DeleteImportedTasks(currentProj, preImportNumOfTasks)
    
                  'If we saved any predecessors, reapply them
    1800          If isSavedTaskLinks Then Call ApplySavedTaskLinks(currentProj, savedTaskLinks)
    
    1850          If Not templateBypass Then
    1900              isTemplateDesired = MsgBox(MSG_IS_TEMPLATE_DESIRED, vbYesNo + R_TO_L, Title:=Application.Name)
    1950          End If
    
    2000          If isTemplateDesired = vbYes Then Call ApplyTemplate(currentProj)
    
                  'Add in the milestone tasks
    2050          If Not currentProj.ProjectSummaryTask.GetField(Application.FieldNameToFieldConstant("Requested Start")) = "NA" Then
    2100              If Not currentProj.ProjectSummaryTask.GetField(Application.FieldNameToFieldConstant("Requested Finish")) = "NA" Then
    2150                  Call AddMilestoneTasks(currentProj)
    2200              Else
    2250                  MsgBox MSG_PRF_MUST_HAVE_REQ_FINISH_DATE, vbCritical + R_TO_L, Title:=Application.Name
    2300                  GoTo ExitSub
    2350              End If
    2400          Else
    2450              MsgBox MSG_PRF_MUST_HAVE_REQ_START_DATE, vbCritical + R_TO_L, Title:=Application.Name
    2500              GoTo ExitSub
    2550          End If
    
                  'Trim the project name
    2600          tempProjName = RTrim(LTrim(tempProjName))
    
                  'Save the file
    2650          Alerts False
    2700          If Not skipSave Then projApp.FileSaveAs Name:="<>\" & tempProjName
    2750          Alerts True
    
    ExitSub:
    2800      projApp.CloseUndoTransaction
    
              'Clear out our refs
    2850      If Not projTasks Is Nothing Then Set projTasks = Nothing
    2900      If Not currentProj Is Nothing Then Set currentProj = Nothing
    2950      If Not projApp Is Nothing Then Set projApp = Nothing
    
    3000      Exit Sub
    
    ErrorHandler:
    3050      ProcessError Err
    3100      GoTo ExitSub
    End Sub
    This here is the example of the excel sheet that is loaded -- it is 60 rows long

    Here is the error message that was returned at first.

    I hope this helps clarify the message.  

    Kathy


    Kathy Tullis

    Monday, April 7, 2014 4:20 PM
  • Kathy,

    Well yes that does provide more information but there are still questions. But before I get into those, let me ask a couple questions I should have asked in the beginning.

    In your initial post you said that the whole process was working fine but now it doesn't. Did you add any new software (applications, utilities, etc.) between the time it was working fine and now? If you did install something on your system, try removing that and see if it works.

    Even if you did not install any new software, did you do anything different with your Project template or Excel files? If so, what?

    You said you are using Project 2007. Is it updated with SP3? I assume you are also using Office 2007. Is it also updated to SP3? If both are not updated to that service pack, install them and see if that helps.

    If you did not install new new software, you did not change your Project or Excel files (other than normal editing) and Project and Office are fully up-to-date with respect to service packs, then unless someone else jumps in and says "I see what is wrong", I think we're going to have to take this to a more one-on-one level. In order to do that I need to actually see your Project and Excel files, (or at least a mockup of each hat demonstrates the problem), along with all the macros. Would you be willing/able to send me that data?

    John

    • Marked as answer by Kathy Tullis Monday, April 7, 2014 9:55 PM
    Monday, April 7, 2014 9:48 PM
  • John,

    I really appreciate all of your help.  I was recently upgraded to Windows 7 and while the PRF Version 7 had been working I did alot of changes to the xls file and had to make changes to PWA custom fields.

    Once Project Pro started shutting down each time I ran the macro I searched and found it said to do the service packs.  Of course we are supposed to get that automatically when we were upgraded.  However, when I looked I did have SP3 but not the April 2013 CU.  I applied that and guess what it worked PERFECTLY.  All of it.

    Thank you again for all your help.  I truely appreciate you taking the time.

    Kathy


    Kathy Tullis

    • Marked as answer by Kathy Tullis Monday, April 7, 2014 9:55 PM
    Monday, April 7, 2014 9:55 PM
  • Kathy,

    You're welcome and thanks for the feedback. I should have asked about the upgrades in my first response.

    John

    Monday, April 7, 2014 10:21 PM