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)

Udometer

Answer 1

"Saved Imports" and "Saved Exports" in Access are stored in ImportExportSpecification objects that form the CurrentProject.ImportExportSpecifications collection. The details of a saved Excel import will look something like the following XML, which I created by doing a manual import of an Excel spreadsheet and ticking the "Save import steps" checkbox on the last page of the import wizard.

<?xml version="1.0" encoding="utf-8" ?>
<ImportExportSpecification Path = "C:\Users\Gord\Desktop\xlsxTest.xlsx" xmlns="urn:www.microsoft.com/office/access/imexspec">
     <ImportExcel FirstRowHasNames="true" Destination="xlsxTest" Range="Sheet1$" >
            <Columns PrimaryKey="ID">
                  <Column Name="Col1" FieldName="ID" Indexed="YESNODUPLICATES" SkipColumn="false" DataType="Long" />
                  <Column Name="Col2" FieldName="TextField" Indexed="NO" SkipColumn="false" DataType="Text" />
                  <Column Name="Col3" FieldName="DateField" Indexed="NO" SkipColumn="false" DataType="DateTime" />
             </Columns>
        </ImportExcel>
</ImportExportSpecification>

The ImportExportSpecification was saved with the name Import-xlsxTest. Now, if I rename the Excel file from "xlsxTest.xlsx" to "anotherTest.xlsx" I can use the following VBA code to change the filename in the ImportExportSpecification's XML and then execute the import:

Option Compare Database
Option Explicit

Sub DoExcelImport()
    Dim ies As ImportExportSpecification, i As Long, oldXML() As String, newXML As String

    Const newXlsxFileSpec = "C:\Users\Gord\Desktop\anotherTest.xlsx"  ' for testing

    Set ies = CurrentProject.ImportExportSpecifications("Import-xlsxTest")
    oldXML = Split(ies.XML, vbCrLf, -1, vbBinaryCompare)
    newXML = ""
    For i = 0 To UBound(oldXML)
        If i = 1 Then  
            ' re-write the second line of the existing XML
            newXML = newXML & _
                    "<ImportExportSpecification Path = """ & _
                    newXlsxFileSpec & _
                    """ xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & _
                    vbCrLf
        Else
            newXML = newXML & oldXML(i) & vbCrLf
        End If
    Next
    ies.XML = newXML
    ies.Execute
    Set ies = Nothing
End Sub

For more information on ImportExportSpecification objects, see

ImportExportSpecification Object (Access)

Constrict

Obviously this should work great and gives good background information, but I found the second sub() at https://mcmap.net/q/367669/-how-can-i-modify-a-saved-microsoft-access-2007-or-2010-import-specification to be more straightforward – Vampire

Answer 2

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

Lindahl

I prefer this approach as this is more robust; one more thing to be added would be that you need to add "Microsoft XML, vX.Y" to "Add Reference" – Tonetic

Answer 3

I researched the same issue. The solution posted by Gord gave me an XML interpretation error. Cosmichighway posted this solution: http://www.utteraccess.com/forum/index.php?showtopic=1981212.

This solution works in Access 2010 and Access 2013 and should also work in Access 2007.

With CurrentProject.ImportExportSpecifications("nameOfSpecification")
    debug.print .XML
    .XML = Replace(.XML, varSavedPathName, varNewPathName)
    debug.print .XML
End With

I was generating a unique filename per export, so I reverted to the original filename path once the process was complete. WorkHoursTransactions is a const. Example:

CONST ConstExportSavedPathName="c:\temp\Name Of File To Use.xls"

tmpFileName = WorkHoursTransactions & ";" & Format(Now(), "YYYYMMDD-HHMMSS") & ".xls"
With CurrentProject.ImportExportSpecifications(WorkHoursTransactions)
    .XML = Replace(.XML, ConstExportSavedPathName, tmpFileName)
    'Debug.Print .XML
End With

DoCmd.OpenReport WorkHoursTransactions, acViewReport, , , acWindowNormal
DoCmd.RunSavedImportExport WorkHoursTransactions

' return to original filename
With CurrentProject.ImportExportSpecifications(WorkHoursTransactions)
    .XML = Replace(.XML, tmpFileName, ConstExportSavedPathName)
    'Debug.Print .XML
End With

I also came across this nice tip to use the immediate window to display the XML. If you have an export specification named 'Export-Table1', then you can paste this in the immediate window to view the XML:

? CurrentProject.ImportExportSpecifications.Item("Export-Table1").XML

Gauguin

Answer 4

In my case

vbCrLf didn't work - but vbLF does!

I'm using Access 2010 (32 bit).

Greetings from Stefan

Mckoy

Answer 5

To add to @Alberts answer, if we have the current file path as a constant, then, when we run the code the next time (for example, the user decides to store the excel file in a different folder after sometime), 'Replace' function will not find the search text as the path was changed in the first run. So, to make it dynamic, we just need to write the current file path to a table when it is replaced by a new path. In the 'Replace' function, we just refer to this value. There is no hard coding of file paths.

Let Current File Path = DLookup("[Current file path]", "File Path Table")
Let New File Path  = DLookup("[New file path]", "File Path Table")
With CurrentProject.ImportExportSpecifications("Saved-Export")
   .XML = Replace(.XML, Current File Path, New File Path)
End With
DoCmd.RunSavedImportExport Saved-Export

'Now you write the 'new file path' to the 'current file path' field in the table

 Set mydb = DBEngine.Workspaces(0).Databases(0)
 Set myset = mydb.OpenRecordset("File Path Table")
 myset.Edit
     Let myset![Current file path] = New File Path
 myset.Update
 myset.Close
 Set myset = Nothing
 Set mydb = Nothing

so the next time it runs , it will pick the correct current file to replace.

Protrusion