如何指定保存Excel导入不同的文件路径 [英] How to specify a different file path for a saved Excel import

问题描述

所以我使用了 doCmd.TransferText 多次使用已保存的文本导入规范的，因为你可以很容易地保存从返回的文件路径 Application.FileDialog（msoFileDialogFilePicker）找到您要使用保存的规范导入选择文件。

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.

不过，我无法找到一个方法来使用Excel文件做同样的，它是简单的保存Excel导入规格，但使用 DoCmd.TransferS preadSheet 办法也没有办法用一个保存的进口，与使用以及 doCmd.RunSavedImportExport 没有选项来指定一个文件路径。

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.

有没有工作，围绕这个除了使用不同的文件类型（如.CSV）

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

推荐答案

保存导入和保存的出口，在访问存储在 ImportExportSpecification 形成的对象 CurrentProject.ImportExportSpecifications 集合。保存Excel导入的细节将类似于下面的XML，这是我做的一个Excel US preadsheet手动导入勾选保存导入步骤复选框导入向导的最后一页上创建的。

"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>

该ImportExportSpecification被保存的名称进口xlsxTest 。现在，如果我从xlsxTest.xlsx重命名的Excel文件anotherTest.xlsx我可以使用下面的VBA code要改变文件名，在ImportExportSpecification的XML，然后执行导入：

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

有关 ImportExportSpecification 对象的详细信息，请参阅

For more information on ImportExportSpecification objects, see

ImportExportSpecification对象（访问）

这篇关于如何指定保存Excel导入不同的文件路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！