'功能:excel文件直接导入 sql server数据库中 '输入:xls_FileName - 要导入的excel文件名,SheetName - 表单名 '备注:在使用这个函数之前,必须保证excel文件是放在服务器上的 '作者:litsnake Public Sub ImportExcelFileIntoSqlServer(Cnn as ADODB.Connection,xls_FileName As String, Optional SheetName As String = "Sheet1") Dim strSQL As String strSQL = "SELECT * FROM OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=" & xls_FileName & "','select * from [" & SheetName & "$]')" Cnn.Execute strSQL End Sub
从数据库里导出这个: dbs.Execute "SELECT * INTO [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] FROM Temp1 是可以的,要反过来的话,那就要一条一条的增了,不能这样成批导入的。有兴趣的话,可以给你一段代码看看。
不好意思,写错了,应该是 '功能:excel文件直接导入 sql server数据库中 '输入:xls_FileName - 要导入的excel文件名 '备注:在使用这个函数之前,必须保证excel文件是放在服务器上的 作者:litsnake Public Sub ImportExcelFileIntoSqlServer(NewTable As String, xls_FileName As String, Optional SheetName As String = "Sheet1") ' Dim Cn As ADODB.Connection Dim strSQL As String strSQL = "SELECT * into " & NewTable & " FROM OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=" & xls_FileName & "','select * from [" & SheetName & "$]')" ' Set Cn = New ADODB.Connection
'输入:xls_FileName - 要导入的excel文件名,SheetName - 表单名
'备注:在使用这个函数之前,必须保证excel文件是放在服务器上的
'作者:litsnake
Public Sub ImportExcelFileIntoSqlServer(Cnn as ADODB.Connection,xls_FileName As String, Optional SheetName As String = "Sheet1")
Dim strSQL As String
strSQL = "SELECT * FROM OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=" & xls_FileName & "','select * from [" & SheetName & "$]')"
Cnn.Execute strSQL
End Sub
dbs.Execute "SELECT * INTO [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] FROM Temp1
是可以的,要反过来的话,那就要一条一条的增了,不能这样成批导入的。有兴趣的话,可以给你一段代码看看。
'功能:excel文件直接导入 sql server数据库中
'输入:xls_FileName - 要导入的excel文件名
'备注:在使用这个函数之前,必须保证excel文件是放在服务器上的
作者:litsnake
Public Sub ImportExcelFileIntoSqlServer(NewTable As String, xls_FileName As String, Optional SheetName As String = "Sheet1")
' Dim Cn As ADODB.Connection
Dim strSQL As String
strSQL = "SELECT * into " & NewTable & " FROM OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=" & xls_FileName & "','select * from [" & SheetName & "$]')"
' Set Cn = New ADODB.Connection
Cnn.Execute strSQL
End Sub