作者:乔山办公网日期:
返回目录:excel表格制作
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"
试试
'sql完整例子
Sub testSql()
'定义连百接对度象
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'定义连接字符串回
Dim conStr As String
Dim sqlstr As String
'连接字符串-以下是连接MSSQL数据库答
conStr = "Provider=sqloledb; " _
& "Server=192.168.1.121; " _
& "Database=DATABASENAME;Uid=admin;Pwd=admin;"
cnn.Open conStr
sqlstr = "SELECT * from tablename"
rs.Open sqlstr, cnn
Range("a2").CopyFromRecordset rs
rs.Close
cnn.Close
End Sub
sub test()
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("Adodb.Recordset")strCn= "Provider=sqloledb;Server=R9HDET7;Database=dbname;Uid=username;Pwd=password"
cnn.Open strCn
SQL = ""
cnn.Open strCn '与数据库建立连接,如果成功,返回zhidao连接对象cn
rs.Open SQL , cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中
cnn.Close
Set cnn = Nothing
end sub
请参内考
连接数据库后,通过写SQL语句实现增删改容查
临时开一百个空度白工作表,保知存内容道后在回Copy过来,例如答:
set wb=Workbooks.Add
wb.sheets(1).Range("A1").CopyFromRecordset rs
wb.sheets(1).UsedRange.Copy Worksheets(3).Range("A17")
wb.Close SaveChange:=False