高手来！关于将Excel中数据用VBA代码读到Access中

高手来！关于将Excel中数据用VBA代码读到Access中

用excel VBA通过ADO和SQL操作Access数据库，所谓操作数据库就是查询、删除、添加数据，你可以用添加数据方法将excel表数据添加到Access数据库。

Private Sub Button3_Released()
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Application.Visible = True
.Application.Workbooks.Add
.Worksheets("Sheet1").Activate
.Columns(1).ColumnWidth = 23
.Columns(2).ColumnWidth = 15
.Columns(3).ColumnWidth = 15
.Columns(4).ColumnWidth = 15
.Columns(5).ColumnWidth = 15
.Columns(6).ColumnWidth = 15
.Columns(7).ColumnWidth = 15
.Columns(18).ColumnWidth = 15
.cells(1, 2).Value = "1o?éè?¨??á?"
.cells(1, 3).Value = "1o?2ú?·??á?"
.cells(1, 4).Value = "2o?éè?¨??á?"
.cells(1, 5).Value = "2o?2ú?·??á?"
.cells(1, 6).Value = "3o?éè?¨??á?"
.cells(1, 7).Value = "3o?2ú?·??á?"
.cells(1, 8).Value = "??·?"
End With
Set cn1 = CreateObject("adodb.connection")
cn1.Open "driver={microsoft access driver (*.mdb)};DBQ=D:\datalog\weight.mdb"
Set rs1 = CreateObject("adodb.recordset")
SQL1 = "select * from FloatTable where marker='s'"
rs1.Open SQL1, cn1
rs1.MoveFirst
i = 2
j = 0
n = 6
Do While Not rs1.EOF
objExcel.Sheets(1).cells(i, 1) = rs1("DateAndTime")
objExcel.Sheets(1).cells(i, 1).numberformatLocal = "yyyy-m-d h:mm:ss"
Do While j < n
objExcel.Sheets(1).cells(i, j + 2) = rs1("Val")
rs1.MoveNext
j = j + 1
Loop
i = i + 1
j = j - n
Loop
rs1.close
Set cn2 = CreateObject("adodb.connection")
cn2.Open "driver={microsoft access driver (*.mdb)};DBQ=D:\datalog\weight.mdb"
Set rs2 = CreateObject("adodb.recordset")
sql2 = "select * from stringTable where marker='s'"
rs2.Open sql2, cn2
rs2.MoveFirst
i = 2
Do While Not rs2.EOF
objExcel.Sheets(1).cells(i, j + 8) = rs2("Val")
rs2.MoveNext
i = i + 1
Loop
rs2.close
End SubPrivate Sub Button3_Released()
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Application.Visible = True
.Application.Workbooks.Add
.Worksheets("Sheet1").Activate
.Columns(1).ColumnWidth = 23
.Columns(2).ColumnWidth = 15
.Columns(3).ColumnWidth = 15
.Columns(4).ColumnWidth = 15
.Columns(5).ColumnWidth = 15
.Columns(6).ColumnWidth = 15
.Columns(7).ColumnWidth = 15
.Columns(18).ColumnWidth = 15
.cells(1, 2).Value = "1o?éè?¨??á?"
.cells(1, 3).Value = "1o?2ú?·??á?"
.cells(1, 4).Value = "2o?éè?¨??á?"
.cells(1, 5).Value = "2o?2ú?·??á?"
.cells(1, 6).Value = "3o?éè?¨??á?"
.cells(1, 7).Value = "3o?2ú?·??á?"
.cells(1, 8).Value = "??·?"
End With
Set cn1 = CreateObject("adodb.connection")
cn1.Open "driver={microsoft access driver (*.mdb)};DBQ=D:\datalog\weight.mdb"
Set rs1 = CreateObject("adodb.recordset")
SQL1 = "select * from FloatTable where marker='s'"
rs1.Open SQL1, cn1
rs1.MoveFirst
i = 2
j = 0
n = 6
Do While Not rs1.EOF
objExcel.Sheets(1).cells(i, 1) = rs1("DateAndTime")
objExcel.Sheets(1).cells(i, 1).numberformatLocal = "yyyy-m-d h:mm:ss"
Do While j < n
objExcel.Sheets(1).cells(i, j + 2) = rs1("Val")
rs1.MoveNext
j = j + 1
Loop
i = i + 1
j = j - n
Loop
rs1.close

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息