给出一个excel文件，怎样用VB把文件中的数据拿出来导入另外一个数据库中。

[yangjianming0822]

给出一个有格式规定的excel文件，怎样用VB把文件中的数据拿出来导入另外一个数据库中。

[yangjianming0822]

谢谢大家的帮助。

[TechnoFantasy]

http://expert.csdn.net/Expert/topic/1593/1593573.xml

http://expert.csdn.net/Expert/topic/1722/1722325.xml
http://search.csdn.net/expert/topic/51/5106/2002/4/4/621990.htm

[yoki]

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.xls;Extended Properties=Excel 8.0"

’如果这张表不存在，你可以使用如下代码：

strSQL = "SELECT * INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 FROM [Sheet1$]"

cn.Execute strSQL, , adExecuteNoRecords

如果表已经存在，您需要添加进数据库，可以使用如下代码：

strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 SELECT * FROM [Sheet1$]"cn.Execute strSQL, , adExecuteNoRecords

[yoki]

Private Sub XlsToMdb(sSheetName As String, _
sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
'功能：将Excel文件中的工作簿导出到Access数据库中的表
'输入参数1、sSheetName：要导出资料的文件名称 (Sheet name)，例如 Sheet1
'输入参数2、sExcelPath：要导出资料的 Excel 档案路径名称 (Workbook path)，例如 C:\book1.xls
'输入参数3、sAccessTable：要导入的 Access Table 名称，例如 TestTable
'输入参数4、sAccessDBPath：要导入的 Access 档案路径名称，例如 C:\Test.mdb
'作者:YOKI 最后修改：2003-08-21
'调用：Call XlsToMdb("c:\book1.xls","c:\test1.mdb",,"$Sheet1","TestTable")
'结果：將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTable

Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
db.Execute("Select * into [;database=" & sAccessDBPath & "]." & sAccessTable & " FROM [" & sSheetName & "$]")
End Sub

你只要加一个获得各个工作表名的循环放在外面即可
Dim cnSourceDb As Adodb.Connection
Dim rsTables As ADODB.Recordset
Dim intCount As Integer

Set cnSourceDb = New ADODB.Connection
cnSourceDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp.xls;Extended Properties=Excel 8.0;Persist Security Info=true"
Set rsTables = New ADODB.Recordset
Set rsTables = cnSource.OpenSchema(adSchemaTables)
If Not rsTables.EOF Then rsTables.MoveFirst
Do While Not rsTables.EOF
If UCase(rsTables!TABLE_TYPE) = "TABLE" Then
Debug.Print "表名:" & rsTables!TABLE_NAME
Call XlsToMdb("c:\book1.xls","c:\test1.mdb",,"& rsTables!TABLE_NAME — ","& rsTables!TABLE_NAME &")
intCount = intCount + 1
End If
rsTables.MoveNext
Loop
rsTables.Close
Set rsTables = Nothing
cnSource.Close
Set cnSource = Nothing

[yoki]

笔误： Call XlsToMdb("c:\book1.xls","c:\test1.mdb",,"& rsTables!TABLE_NAME & ","& rsTables!TABLE_NAME &")

[rainstormmaster]

up