How to specify a different file path for a saved Excel import

Question

So I have used doCmd.TransferText many times to use a saved text import specification, as you can easily saved the file path returned from an Application.FileDialog(msoFileDialogFilePicker) to find a select the file you wish to import with the saved specification.

However I am having trouble finding a way to do the same with an excel file, it is simple to save an excel import specification, but using the DoCmd.TransferSpreadSheet method there is no way to used a saved import, as well using doCmd.RunSavedImportExport has no option to specify a file path.

Is there any work around for this other than using a different file type (e.g. .csv)

Answer 1

Saw this and thought I'd share something I worked up a while back to solve the problem. Gives more control over what you can change in the specification:

' MSXML2 requires reference to "Microsoft XML, v6.0"
' earlier versions are probably compatible, remember to use the appropriate DOMDocument object version.
Sub importExcelFile(ImportSpecName As String, Filename As String, SheetName As String, OutputTableName As String)
    Dim XMLData As MSXML2.DOMDocument60
    Dim ImportSpec As ImportExportSpecification
    Dim XMLNode As IXMLDOMNode

    ' Get XML object to manage the spec data
    Set XMLData = New MSXML2.DOMDocument60

    XMLData.async = False
    XMLData.SetProperty "SelectionLanguage", "XPath"
    XMLData.SetProperty "SelectionNamespaces", "xmlns:imex='urn:www.microsoft.com/office/access/imexspec'"
        ' need to rename the default namespace, so that we can XPath to it. New name = 'imex'

    ' existing Import Specification (should be set up manually with relevant name)
    Set ImportSpec = CurrentProject.ImportExportSpecifications(ImportSpecName)
    XMLData.LoadXML ImportSpec.XML

    ' change it's path to the one specified
    With XMLData.DocumentElement
        .setAttribute "Path", Filename
        ' Destination attribute of the ImportExcel node
        Set XMLNode = .SelectSingleNode("//imex:ImportExcel/@Destination")    ' XPath to the Destination attribute
        XMLNode.Text = OutputTableName
        ' Range attribute of the ImportExcel node
        Set XMLNode = .SelectSingleNode("//imex:ImportExcel/@Range")    ' XPath to the range attribute
        XMLNode.Text = SheetName & "$"
    End With

    ImportSpec.XML = XMLData.XML

    ' run the updated import
    ImportSpec.Execute

End Sub