How to specify a different file path for saved Excel import

Question

So I used doCmd.TransferText many times to use the saved text import specification, since you can easily save the file path returned from Application.FileDialog(msoFileDialogFilePicker) to find the file you want to import with the saved specification.

However, it’s hard for me to find a way to do the same with the excel file, just save the excel import specification, but using the DoCmd.TransferSpreadSheet method DoCmd.TransferSpreadSheet no way to use the saved import, and use doCmd.RunSavedImportExport not able to specify the path to the file.

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

Answer 1

"Saved imports" and "Saved exports" in Access are stored in ImportExportSpecification objects that make up the CurrentProject.ImportExportSpecifications collection. The details of the saved Excel import will look something like this: the next XML that I created by manually importing an Excel spreadsheet and checking 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 function has been 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 file name in the ImportExportSpecification XML document and then import it:

 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 about ImportExportSpecification objects ImportExportSpecification see

ImportExportSpecification Object (Access)

Answer 2

I investigated the same problem. The solution posted by Gord gave me an XML interpretation error. Cosmichighway published 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 created a unique file name for export, so after the process was completed, I returned the original path to the file name. WorkHoursTransactions is 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 stumbled upon this good tip to use an immediate window to display XML. If you have an export specification with the name "Export-Table1", you can paste it in the next window to view the XML:

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

Answer 3

I saw this and thought that I would share what I worked on a while ago 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 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 

Answer 4

In my case

vbCrLf does not work, but vbLF does!

I am using Access 2010 (32 bit).

Greetings from Stephen

Answer 5

To add an answer to @Alberts, if we have the current file path as a constant, then the next time we run the code (for example, the user decides to save the excel file in another folder after some time), the Replace function will not find the text search because the path was changed in the first run. Thus, to make it dynamic, we just need to write the current path to the table when it is replaced with the new path. In the Replace function, we simply 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 starts, it will select the correct current file to replace.