Sub addRecords()
'引用Microsoft ActiveX Data Objects 2.x Library
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
    Dim strMsg As String
    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\学校管理.accdb"
    SQL = "SELECT A.* FROM [Excel 12.0;Database=" & ThisWorkbook.FullName & ";].[课程$B2:F" & Range("b" & Rows.Count).End(xlUp).Row _
        & "] A LEFT JOIN 课程设计 B ON A.学生编号=B.学生编号 AND A.学生姓名=B.学生姓名 AND A.班级=B.班级 AND A.选课课程=B.选课课程 WHERE B.学生编号 IS NULL"
    rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        SQL = "INSERT INTO 课程设计 " & SQL
        cnn.Execute SQL
        strMsg = rs.RecordCount & "条记录已添加到数据库！"
    Else
        strMsg = "没有发现可以插入的记录！"
    End If
    MsgBox strMsg, vbInformation, "提示"
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

如何把  ThisWorkbook.FullName  转为成打开文件夹选择呢

    Set FileDialogObject = Application.FileDialog(msoFileDialogFilePicker)
    With FileDialogObject
        .Title = "请选择文件"
        .InitialFileName = ThisWorkbook.Path
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
    End With
    FileDialogObject.Show
    Set ??? = FileDialogObject.SelectedItems
    If ???.Count = 0 Then Exit Sub

这个论坛好像没什么人气

Sub addRecords()
'引用Microsoft ActiveX Data Objects 2.x Library
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
    Dim strMsg As String
    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\学校管理.accdb"
    SQL = "SELECT A.* FROM [Excel 12.0;Database=" & ThisWorkbook.FullName & ";].[课程$B2:F" & Range("b" & Rows.Count).End(xlUp).Row _
        & "] A LEFT JOIN 课程设计 B ON A.学生编号=B.学生编号 AND A.学生姓名=B.学生姓名 AND A.班级=B.班级 AND A.选课课程=B.选课课程 WHERE B.学生编号 IS NULL"
    rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        SQL = "INSERT INTO 课程设计 " & SQL
        cnn.Execute SQL
        strMsg = rs.RecordCount & "条记录已添加到数据库！"
    Else
        strMsg = "没有发现可以插入的记录！"
    End If
    MsgBox strMsg, vbInformation, "提示"
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub


老师您好，上面的代码是从Excel 导入Access ,但是不能指定列，有的列不用导入                     
问题出现在这个   SQL = "INSERT INTO 课程设计 " & SQL
如果才能指定整列导入呢

[此贴子已经被作者于2020-7-27 00:02编辑过]