none
FileOpenEX trouble with importing data from xlsx, code stops working RRS feed

  • Question

  • Hi all,

    I wrote some code to import data from an xlsx file and use a map with a user defined merge key.

    I import Start10 and Finish10 fields, name and this userdefined merge field, of course there is a fitting field in project too.

    What happens:

    first import: changed tasks from excel are going to project, but the gantt bar is not displayed

    next import: (same code) changed values are going to project like first import + gantt bar is shown as expected

    but: after doing the second import, the code stops working (nothing happens) and when I close project it asks for save the changes in the imported EXCEL file ...

    any ideas???

    System: win10, project 2016, excel2016

    happens in project 2013 too

    Sub import_from_TR_easy()
    
    ' Easy Import from an Excel File with  FileDialog
    ' import of all tasks with merge key
    '
    
    On Error GoTo err_import
      
                
        MapEdit Name:="import_easy_1", Create:=True, OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True, _
                TableName:="project_ID_" & ActiveProject.ProjectSummaryTask.Text30, FieldName:="_unique_report_id", ExternalFieldName:="_unique_report_id", ExportFilter:="all tasks", _
                ImportMethod:=2, MergeKey:="_unique_report_id", HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), _
                TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False
    
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="_Project_ID", ExternalFieldName:="_Project_ID"
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="_to_report", ExternalFieldName:="_to_report"
    
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="Name", ExternalFieldName:="Name"
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="_Outline_Tracker", ExternalFieldName:="_Outline_Tracker"
    
    
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="Start10", ExternalFieldName:="Start10"
        MapEdit Name:="import_easy_1", DataCategory:=0, FieldName:="Finish10", ExternalFieldName:="Finish10"
    
    
    ' 22.5.2018 FileOpen per BrowseFileOpen mit Angabe der Import Spezifikation
    
        Dim Browse As New clsBrowse
        Dim EXC_FileName As String
    
        With Browse
            .DialogTitle = "My File Open Dialog"
            .Filter = "Excel Files|*.xls*"
            .ShowOpen
            If .FileName = "" Then
                MsgBox "You clicked the Cancel Key"
            Else
                EXC_FileName = .FileName
    '            MsgBox EXC_FileName
            End If
        End With
    
    
        FileOpenEx EXC_FileName, map:="import_easy_1"
        
      
        Exit Sub
       
       
    err_import:
    
        MsgBox Err.Number & " " & Err.Description
    
        
    End Sub




    Saturday, May 26, 2018 9:50 AM

All replies

  • Peter,

    Why use an import map? I gave you VBA code to import data from Excel, did that not work?

    John

    Saturday, May 26, 2018 2:38 PM
  • John,

    the sponsor asked me to show a second way to import data with lightly coding. If that doesn't run  smoothly, I'll come back to the Excel Import Coding ...

    For presentation I needed a faster method to show a couple of things. But as you see, there are issues ...

    Doing it completely in VBA takes more time

    Peter


    Saturday, May 26, 2018 3:17 PM