標題: vba使用ADO將EXCEL資料導入到SQL Server [打印本頁] 作者: 9100bo 時間: 2017-6-3 16:17 標題: vba使用ADO將EXCEL資料導入到SQL Server
Sub test()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=xxx;" & _
"Initial Catalog=Mydata;User ID=sa;Password=54123"
'Import by using OPENROWSET and SELECT query.
strSQL = "SELECT * INTO Newtable FROM " & _
"OPENROWSET('Microsoft.ACE.OLEDB.12.0', " & _
"'Excel 12.0;Database=C:\Users\My\Desktop\222.xlsx', " & _
"[Sheet1$])"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
End Sub
[attach]27269[/attach]
使用sql server連結伺服器Excellink
Select * From Openquery(Excellink,'Select * from [sheet1$]')
可以正確導入Excel數據到Sql Server
