【フォームからユーザーにインポート項目を選択させる】

Option Compare Database
Option Explicit
Dim DataFolder As String

Private Sub Form_Open(Cancel As Integer)
    Dim FName As String

    DataFolder = Application.CurrentProject.Path & "\Data"
    FName = Dir(DataFolder & "\", vbNormal)
    Me.cbData = FName
    Do While FName <> ""
        Me.cbData.AddItem FName
        FName = Dir
    Loop
    If Me.cbData <> "" Then cbData_AfterUpdate
   
End Sub

Private Sub cbData_AfterUpdate()
    Dim stSQL As String
   
    stSQL = "SELECT * FROM [Text;DATABASE=" & _
        DataFolder & ";HDR=YES;].[" & Me.cbData & "];"
    Me.lstSelField.RowSource = stSQL
End Sub

Private Sub cmdImport_Click()
    Dim varItm As Variant
    Dim stSQL As String

    For Each varItm In Me.lstSelField.ItemsSelected
        stSQL = stSQL & ",[" & Me.lstSelField.ItemData(varItm) & "]"
    Next varItm
   
    If stSQL <> "" Then
        If DCount("*", "MSysObjects", "Name='" & Me.txtTableName & "' and Type=1") > 0 Then
            DoCmd.DeleteObject acTable, Me.txtTableName
        End If
        stSQL = "SELECT " & Mid(stSQL, 2) & " INTO [" & Me.txtTableName & "]" & _
            Mid(Me.lstSelField.RowSource, 9)
            Debug.Print stSQL
        CurrentDb.Execute stSQL, dbFailOnError
        Application.RefreshDatabaseWindow
    Else
        MsgBox "リストボックスでフィールドを選択してください。"
    End If

End Sub