VB把excel数据导入ACCESS

Dim SQL As String

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

Dim Constr As String

Constr = "PROVIDER = Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db.mdb"

conn.Open Constr

SQL = "select *  into t2 from  [excel 8.0;database=c:\book1.xls].[sheet1$]"

conn.Execute SQL

conn.Close

Set conn = Nothing

http://www.cnblogs.com/wsoft/articles/1960028.html

 

xls文件到mdb,用AutoHotkey实现如下：
conn:=ComObjCreate("ADODB.connection")
connectionStrings := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" A_ScriptDir "\test\TestDB.mdb"
conn.Open(connectionStrings)
 Sql:="Select * INTO test4 FROM [Excel 8.0;HDR=Yes;IMEX=1;DATABASE=X:\AHK\CSV\test.xls].[Sheet1$];"
conn.execute(Sql)
conn.Close
conn:=
return

 

建议用

Microsoft.ACE.OLEDB.12.0 代替Microsoft.Jet.OLEDB.4.0

用Excel 12.0 Xml; 代替Excel 8.0;