vba 如何为保存的 Excel 导入指定不同的文件路径

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/23350640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 02:55:16  来源:igfitidea点击:

How to specify a different file path for a saved Excel import

vbams-accessaccess-vba

提问by pegicity

So I have used doCmd.TransferTextmany 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.

所以我doCmd.TransferText多次使用保存的文本导入规范,因为您可以轻松保存从 an 返回的文件路径Application.FileDialog(msoFileDialogFilePicker)以找到选择您希望使用保存的规范导入的文件。

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.TransferSpreadSheetmethod there is no way to used a saved import, as well using doCmd.RunSavedImportExporthas no option to specify a file path.

但是,我找不到对 excel 文件执行相同操作的DoCmd.TransferSpreadSheet方法,保存 excel 导入规范很简单,但是使用该方法无法使用已保存的导入,并且 usingdoCmd.RunSavedImportExport也无法指定文件路径。

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

除了使用不同的文件类型(例如 .csv)之外,还有其他解决方法吗?

采纳答案by Gord Thompson

"Saved Imports" and "Saved Exports" in Access are stored in ImportExportSpecificationobjects that form the CurrentProject.ImportExportSpecificationscollection. 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.

Access 中的“保存的导入”和“保存的导出”存储在ImportExportSpecification形成CurrentProject.ImportExportSpecifications集合的对象中。保存的 Excel 导入的详细信息类似于以下 XML,我通过手动导入 Excel 电子表格并勾选导入向导最后一页上的“保存导入步骤”复选框来创建该 XML。

<?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:

ImportExportSpecification 以名称 保存Import-xlsxTest。现在,如果我将 Excel 文件从“xlsxTest.xlsx”重命名为“anotherTest.xlsx”,我可以使用以下 VBA 代码更改 ImportExportSpecification 的 XML 中的文件名,然后执行导入:

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 ImportExportSpecificationobjects, see

有关ImportExportSpecification对象的更多信息,请参见

ImportExportSpecification Object (Access)

导入导出规范对象(访问)

回答by Jason Hardman

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

回答by Albert

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.

我研究了同样的问题。Gord 发布的解决方案给了我一个 XML 解释错误。Cosmichighway 发布了这个解决方案: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.

此解决方案适用于 Access 2010 和 Access 2013,也适用于 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:

我每次导出都会生成一个唯一的文件名,因此一旦该过程完成,我就恢复到原始文件名路径。WorkHoursTransactions 是一个常量。例子:

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:

我还遇到了这个使用即时窗口显示 XML 的好技巧。如果您有一个名为“Export-Table1”的导出规范,那么您可以将其粘贴到即时窗口中以查看 XML:

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

回答by Gopa Kumar

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.

添加到@Alberts 答案中,如果我们将当前文件路径作为常量,那么,当我们下次运行代码时(例如,用户决定在某个时间后将 excel 文件存储在不同的文件夹中),'替换' 函数将找不到搜索文本,因为路径在第一次运行时发生了变化。因此,要使其动态化,我们只需要在将当前文件路径替换为新路径时将其写入表即可。在“替换”函数中,我们只引用这个值。文件路径没有硬编码。

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.

所以下次运行时,它会选择正确的当前文件来替换。

回答by Stefan Heicking

In my case

就我而言

vbCrLfdidn't work - but vbLFdoes!

vbCrLf不起作用 - 但vbLF 起作用了

I'm using Access 2010 (32 bit).

我正在使用 Access 2010(32 位)。

Greetings from Stefan

斯蒂芬的问候