How to specify a different file path for a saved Excel import
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)
"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
Run a saved import or export specification - Access, To import a text file, you can open the file or import the text file as an external data range. To export data from Excel to a text file, use the Save As command and Select Text Files in the file type dropdown list in the Open dialog box. If you want more flexibility in converting columns to different data formats, you can use Insert the current file name, its full path, and the name of the active worksheet. Type or paste the following formula in the cell in which you want to display the current file name with its full path and the name of the current worksheet: =CELL("filename") Insert the current file name and the name of the active worksheet
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
Manage Data Tasks - Dialog - Access - office support, If the file that you import into Access is a CSV/Text/Excel file, then you can write a "yyyymmdd") PATH = "location where the CSV file is stored\prefix name" structure specified in the VBA; and/or; The file is currently opened by another user. The Excel Options dialog box appears. Click the Save tab. The Save options appear in the right pane. Click in the Default File Location text box.
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:
Import or export text (.txt or .csv) files - Excel, Learn how to run a saved Access import or export specification from within Access or If the only difference between the operation you want to run and an existing If the specification exports formatted data to an Excel workbook or a Word file (when you are running an export operation), click the path of the file to edit it. Or you can use different File Name and Location to Save the File. You can also set to an object and Save the File. In other method, you use Save Dialog Box. So that user can choose a specific folder to save the Excel File.
In my case
vbCrLf didn't work - but vbLF does!
I'm using Access 2010 (32 bit).
Greetings from Stefan
Access VBA to Import a File with Variable Name, If you need to stuff the current workbook's filename and path into a cell or a header or When you import information from a CSV text file, Excel formats the data according to Excel provides different file formats you can use to export your worksheet information. Specifying a Delimiter when Saving a CSV File in a Macro Edited to remove the solution with os.path.abspath. After the comment below this answer, I read the documentation myself and realized that it has a different purpose. Although I have used it in the past to make my code dual OS friendly, because it neatly appends CWD to the path, and changes / to \\ when moving from Debian to Windows and vice versa.
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.
Files (Microsoft Excel) - Microsoft Excel Tips, To set a default location: Put the cursor in the Location field. Click the on the far right. In the new window, find the desired file location or folder to save to. Click OK. Click Apply. Click OK. No reason to import os. pandas automatically find the CSV or any other dataset file from where your notebook is running but os.chdir() is just to change the working directory location from where you want to pick the multiple data.
What do the paths on the File Locations tab of User Options do , Sometimes we need to copy file names, path or extension in excel files. Now what if you Duration: 2:59 Posted: Nov 12, 2017 How to specify a different file path for a saved Excel import. 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.
How to Import Multiple File Names into Cells in Excel, They will get Excel's open File Dialog Box and they can browse and select You can Duration: 10:00 Posted: Sep 26, 2019 Connect to an XML file Click on the Data tab, then Get Data > From File > From XML. If you don't see the Get Data button, click New Query > From File > From XML. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.
VBA to BROWSE & COPY Data from SELECTED File in Excel , How-To; Export; Import; Importing multiple versions of a file? Editing the Select the action you want to perform from the Migration menu or from Explorer. You can choose the location where you want to save the Excel file. Copying or moving an Excel-exported PDF file automatically to a specific folder location or local directory can be very easily achieved if you try to run a customized VBA macro program in Microsoft Excel, which essentially enables the user to freely select any desired file and folder location for output purpose.