VB 操作Excel总结

1.将Excel的数据导入数据库

Dim Conn As ADODB.Connection

Set Conn = New ADODB.Connection

Conn.Open "连接到你的数据库XJGL.MDB的字符串"

Conn.Execute "select * into tmptable from [excel 8.0;database=" + 你的excel表名 + "].[sheet名$]"

Conn.Execute "insert into xsda(学籍号,准考证号,姓名,性别,出生年月,班级) select 学籍号,准考证号,姓名,性别,出生年月,班级 from tmptable"

Conn.Execute "drop tabel tmptable"

Set Conn = Nothing

2.读写Excel文件

Dim xlApp As Object

Dim xlBook As Object

Dim xlSheet As Object

On Error Resume Next

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls")

Set xlSheet = xlBook.Worksheets(1)

xlApp.Visible = False

xlSheet.Activate

'处理数据,填充Excel表

xlSheet.Cells(3, 4) = "test"

xlApp.Visible = True '显示Excel

Set xlApp = Nothing '交还控制给Excel

Set xlBoook = Nothing

Set xlSheet = Nothing