|
楼主 |
发表于 2024-11-10 21:35
|
显示全部楼层
谢谢大家!!!小弟用AI写成功了
Sub ImportDataToAccess()
Dim conn As Object
Dim rs As Object
Dim strFile As String
Dim strConn As String
Dim strSQL As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rngData As Range
Dim row As Range
' 设置Access数据库文件路径
strFile = "d:\ABC.mdb"
' 设置连接字符串
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
' 创建ADODB连接对象
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' 打开连接
On Error GoTo ErrorHandler
conn.Open strConn
' 设置Excel工作簿和工作表
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
' 设置要导入的数据范围
Set rngData = ws.Range("B2:D" & ws.Cells(ws.Rows.Count, "A").End(xlUp).row)
' 循环遍历数据并将其插入到Access数据库中
For Each row In rngData.Rows
' 创建SQL语句
strSQL = "INSERT INTO mei (名称, 价格, 日期) VALUES ('" & row.Cells(1).Value & "', " & row.Cells(2).Value & ", #" & Format(row.Cells(3).Value, "yyyy-mm-dd") & "#)"
' 执行SQL语句
conn.Execute strSQL
Next row
' 关闭连接并释放对象
conn.Close
Set conn = Nothing
Set rs = Nothing
MsgBox "数据导入成功!"
Exit Sub
ErrorHandler:
MsgBox "发生错误: " & Err.Description, vbCritical, "错误"
If Not conn Is Nothing Then
If conn.State = 1 Then conn.Close
End If
Set conn = Nothing
Set rs = Nothing
End Sub
|
|