Access 2016 - Update Import Specifications

Question

How do I update an existing saved import specification for importing an Excel Spreadsheet.  It was easy to do in previous versions of Access.  Thanks, Kevin

Answer 1

Are you talking about the saved import or export specifications that are created when you tell the import/export wizard to save the steps?  I don't recall that there was ever an easy way to do that for Excel files (unlike text files).

What you can do is extract the spec from CurrentProject.ImportExportSpecifications, get the XML from it, modify the XML, and stuff the modified XML back in the spec.

You can list the current saved import specs using a procedure like this:

'------ start of code ------

Sub ListSavedImportExportsByIndex(Optional includeXML As Boolean = False)

    Dim i As Long

    Dim specCount As Long

    Debug.Print "Import/Export Specifications"

    specCount = CurrentProject.ImportExportSpecifications.Count

    On Error Resume Next

    For i = 0 To specCount - 1

        Debug.Print "--------------------"

        Err.Clear

        Debug.Print CurrentProject.ImportExportSpecifications(i).Name

        If Err.Number = 0 Then

            If includeXML Then

                If Err.Number = 0 Then

                    Debug.Print CurrentProject.ImportExportSpecifications(i).XML

                Else

                    Debug.Print "* Error getting XML for index "; i; ": "; Err.Number; " - "; Err.Description

                End If

            End If

        Else

            Debug.Print "* Error getting name for index "; i; ": "; Err.Number; " - "; Err.Description

        End If

        Debug.Print "--------------------"

    Next i

    Debug.Print "*** " & CurrentProject.ImportExportSpecifications.Count & " specifications found."

End Sub

'------ end of code ------

And execute it in the Immediate Window, telling it to output the XML as well as the names, by entering this command:

    ListSavedImportExportsByIndex True

It would be perfectly feasible to create a form to extract and display the details of a specification, allowing you to update the XML and save it back in the spec by assigning the modified XML to the specs .XML property.