如何将EXCEL格的指定格的数据导入SQL 2000中?(需原代码) 如何将EXCEL格的指定格的数据导入SQL 2000中?(需原代码) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 EXCEL文件要设置固定格式,还要设置命名范围,然后才可以导入 大致思路如下: '// 设置打开 EXCEL 文件的连接字符串 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=excel文件名;Extended Properties=Excel 8.0" '// 以记录集的形式打开 Excel 文件,adoConn 为 ADODB.Connection 对象 adoConn.Open strConn '// 将数据插入到指定的表中(以ODBC的方式打开SQL数据库) strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=服务器IP;Database=数据库;UID=用户名;PWD=密码].SQL中的表名 SELECT EXCEL中的字段 FROM EXCEL工作表名" '// 执行导入语句 adoConn.Execute strSQL, , adExecuteNoRecords Function exportExcelToAccess(ByVal sSheetName As String, _ByVal sExcelPath As String, ByVal sAccessTable As String, ByVal sAccessDBPath As String)'''''''''''''''' DAO 方式'Dim db As Database'Dim rs As Recordset'Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")'Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & _'sAccessTable & " FROM [" & sSheetName & "$]")''MsgBox "Table exported successfully!", vbInformation''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ADO 方式Dim Cn As ADODB.ConnectionSet Cn = New ADODB.ConnectionCn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & sExcelPath & ";Extended Properties = Excel 8.0;"Cn.OpenCall Cn.Execute("Select * into [;database=" & sAccessDBPath & "]." & _ sAccessTable & " FROM [" & sSheetName & "$]")'Set Cn = Nothing'MsgBox "Table exported successfully!", vbInformationEnd Function'调用exportExcelToAccess "Sheet1", App.Path & "\middle\info.xls", "info", App.Path & "\db\db1.mdb" 怎么才能让新创建的线程运行一个Form或者webbrowser? VB如何实现打开我的电脑,我的文档等功能 来啊,散分拉!!!!!!!!!!!1111 急急!!在线等待 怎样用ADO给ACCESS添加记录? compile error:User-defined type not defined ,高手帮帮忙,必给分! 有关利用VB和SQL SERVER的问题。 象网络蚂蚁那样的下载程序 用过水晶报表的高手,请帮个忙! 如何用VB打印控件中的内容? 水晶报表记录集的问题 如何判断EXCEL文件是否已运行?
大致思路如下:
'// 设置打开 EXCEL 文件的连接字符串
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=excel文件名;Extended Properties=Excel 8.0" '// 以记录集的形式打开 Excel 文件,adoConn 为 ADODB.Connection 对象
adoConn.Open strConn
'// 将数据插入到指定的表中(以ODBC的方式打开SQL数据库)
strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=服务器IP;Database=数据库;UID=用户名;PWD=密码].SQL中的表名 SELECT EXCEL中的字段 FROM EXCEL工作表名"
'// 执行导入语句
adoConn.Execute strSQL, , adExecuteNoRecords
ByVal sExcelPath As String, ByVal sAccessTable As String, ByVal sAccessDBPath As String)'''''''''''''''' DAO 方式
'Dim db As Database
'Dim rs As Recordset
'Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
'Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & _
'sAccessTable & " FROM [" & sSheetName & "$]")
'
'MsgBox "Table exported successfully!", vbInformation
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''' ADO 方式
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & sExcelPath & ";Extended Properties = Excel 8.0;"
Cn.OpenCall Cn.Execute("Select * into [;database=" & sAccessDBPath & "]." & _
sAccessTable & " FROM [" & sSheetName & "$]")
'Set Cn = Nothing'MsgBox "Table exported successfully!", vbInformationEnd Function'调用
exportExcelToAccess "Sheet1", App.Path & "\middle\info.xls", "info", App.Path & "\db\db1.mdb"