MS Access Form按钮，允许用户浏览/选择文件，然后将文件导入表 [英] MS Access Form button that allows user to browse/choose file, then imports file to a table

问题描述

在我的数据库中，我可以使命令按钮使用以下命令导入文件：
DoCmd.TransferText acImportDelim，来自Import的原始数据_ Import规范，来自Import的原始数据，D：\Users \Denise_Griffith\文档\Griffith\PRIME RECON FILES\jdaqawmslesfilesemailDLX_SHPREC_2017-04-26_03-33-47.csv，是，

In my database, I can made a command button import a file using the following: DoCmd.TransferText acImportDelim, "Raw Data from Import_ Import Specification", "Raw Data from Import", D:\Users\Denise_Griffith\Documents\Griffith\PRIME RECON FILES\jdaqawmslesfilesemailDLX_SHPREC_2017-04-26_03-33-47.csv, True, ""

但是我会希望用户选择要导入的文件，因为文件名每天都会根据创建日期和时间而有所不同。我找到了此网站（ http://access.mvps.org/access/api/ api0001.htm ），并且能够弹出对话框以允许用户浏览和选择文件，但是我不知道如何合并它，因此所选文件是使用我创建的规范导入的，

But I would like to have the user choose the file to import, since the filename is different every day based on date and time it was created. I have found this site (http://access.mvps.org/access/api/api0001.htm) and was able to get the dialog to pop up to allow the user to navigate and select the file, but I do not know how to incorporate it so the file selected is imported using the specification I created, and into the appropriate table.

请帮助！

推荐答案

您需要设置对 Microsoft Office对象库的引用。

Public Sub ImportDocument()
    On Error GoTo ErrProc

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .InitialFileName = "Some folder"
        .Title = "Some Title"
        With .Filters
            .Clear
            .Add "CSV documents", "*.csv", 1
        End With
        .ButtonName = " Import Selected "
        .AllowMultiSelect = False

        If .Show = 0 Then GoTo Leave
    End With

    Dim selectedItem As Variant
    For Each selectedItem In fd.SelectedItems
        DoCmd.TransferText acImportDelim, "Raw Data from Import_ Import Specification", "Raw Data from Import", selectedItem, True, ""
    Next

Leave:
    Set fd = Nothing
    On Error GoTo 0
Exit Sub

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

在用户评论后更新：

必须将 Sub 更改为 Function 并检查返回值。

You must change the Sub to a Function and check the return value.

最简单的方法是返回 Boolean ，其中 FALSE 表示已中止， TRUE 表示成功。但是，这样做可以排除 Error 情况，因为Aborted和Error都将返回 FALSE 。

The simplest way is to return a Boolean, where FALSE indicates aborted and TRUE indicates success. However by doing so, you exclude the Error scenario as both Aborted and Error will return FALSE.

因此，您可以返回 Long 值，例如0、1、2分别指示异常终止，成功和错误。为了避免出现幻数，我将创建并返回一个 Enum 类型，如下所示：

Therefore you can return a Long value e.g. 0, 1, 2 indicating Aborted, Success and Error respectively. In order to avoid the "magic numbers" though, I would create and return an Enum type as shown below:

Public Enum TaskImportEnum
    Aborted = 0 'default
    Success
    Failure
End Enum

Public Function ImportDocument() As TaskImportEnum
    On Error GoTo ErrProc

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .InitialFileName = "Some folder"
        .Title = "Dialog Title"
        With .Filters
            .Clear
            .Add "CSV documents", "*.csv", 1
        End With
        .ButtonName = " Import Selected "
        .AllowMultiSelect = False   'Change this to TRUE to enable multi-select

       'If aborted, the Function will return the default value of Aborted
        If .Show = 0 Then GoTo Leave
    End With

    Dim selectedItem As Variant
    For Each selectedItem In fd.SelectedItems
        DoCmd.TransferText acImportDelim, "Raw Data from Import_ Import Specification", "Raw Data from Import", selectedItem, True, ""
    Next selectedItem

   'Return Success
   ImportDocument = TaskImportEnum.Success

Leave:
    Set fd = Nothing
    On Error GoTo 0
    Exit Function

ErrProc:
    MsgBox Err.Description, vbCritical
    ImportDocument = TaskImportEnum.Failure  'Return Failure if error
    Resume Leave
End Function

最后，您可以这样调用函数：

Lastly, you can call the Function like this:

Sub Import()

    Dim status_ As TaskImportEnum
        status_ = ImportDocument

    Select Case status_
        Case TaskImportEnum.Success:
            MsgBox "Success!"

        Case TaskImportEnum.Failure:
            MsgBox "Failure..."

        Case Else:
            MsgBox "Aborted..."
    End Select

End Sub

您可以阅读有关<$ c的更多信息$ c>枚举在此处键入： http：//www.cpearson。 com / excel / Enums.aspx

You can read more about the Enum type here: http://www.cpearson.com/excel/Enums.aspx

这篇关于MS Access Form按钮，允许用户浏览/选择文件，然后将文件导入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！