Menu
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)
291
To add the file path, go to the Insert tab and click on the "Hyperlink" button. In the "Insert Hyperlink" window, click on the "Browse" button. This will open up a "Choose File" window. Find the file that you want to link to and click on the "Open" button.
Saved Imports and Saved Exports tabs To view the Manage Data Tasks dialog in Access click External Data > Saved Imports or External Data > Saved Exports. You can click either of the tabs on the dialog box to switch between the saved import and saved export specifications.
Hold shift button down while clicking the right mouse button (Shift+Right-Click). Choose "Copy as Path". Return to the Spreadsheet and choose "Paste" to paste the list of documents into the spreadsheet. This will paste the full path, including the filename, of each document, as shown below.
Access creates and stores the specification in the current database. If you clicked Create Outlook Task on either the Save Import Steps or Save Export Steps page of the wizard, an Outlook Task window appears. Fill in the details of the task and then click Save & Close.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
