Dim catDB As ADOX.Catalog Dim tblLink As ADOX.Table Set catDB = New ADOX.Catalog 'Link the Orders_Table in the Workbook to the Access Northwind Database. 'The name of the linked, or attached table, in NorthWind will be "LinkedXLS" If Conns.State <> 1 Then Call sqlserverconnection End If 'catDB.ActiveConnection = Conns '.ConnectionString catDB.ActiveConnection = Conns.ConnectionString Set tblLink = New ADOX.Table With tblLink .Name = "LinkedXLS" Set .ParentCatalog = catDB .Properties("Jet OLEDB:Create Link") = True .Properties("Jet OLEDB:Link Provider String") = _ "Excel 8.0;DATABASE=" & strpathe & ";HDR=NO" .Properties("Jet OLEDB:Remote Table Name") = str_tblname End With catDB.Tables.Append tblLink 'Append the records from a join between [Order Details] and [Products] into the 'linked Excel table Conns.Execute "INSERT INTO disgner_begin " & strsqlfields & "values " & _ "SELECT " & strexcelfields & " from LinkedXLS" 'Detach the table and close the connection catDB.Tables.Delete "LinkedXLS" Set tblLink = Nothing Set catDB = Nothing
引用ado. Ext.2.6 for dll............ 但是我的sql server 用的是ole db 连接 excel 用的是jet oledb 所以搞不定了!! 可能真的无药可救了。害的俺白花心思。呜呜
'No Problem '参阅 T-SQL 帮助: OPENROWSET() 的用法 Dim adoConnection As New ADODB.Connection adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=test" adoConnection.Execute "INSERT INTO Table1 (f1,f2,f3) SELECT * FROM OPENROWSET('MSDASQL','DRIVER={Microsoft Excel Driver (*.xls)};DBQ=d:\excel97.xls','select * from [test$]')"
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table Set catDB = New ADOX.Catalog 'Link the Orders_Table in the Workbook to the Access Northwind Database.
'The name of the linked, or attached table, in NorthWind will be "LinkedXLS" If Conns.State <> 1 Then
Call sqlserverconnection
End If 'catDB.ActiveConnection = Conns '.ConnectionString
catDB.ActiveConnection = Conns.ConnectionString
Set tblLink = New ADOX.Table
With tblLink
.Name = "LinkedXLS"
Set .ParentCatalog = catDB
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = _
"Excel 8.0;DATABASE=" & strpathe & ";HDR=NO"
.Properties("Jet OLEDB:Remote Table Name") = str_tblname
End With
catDB.Tables.Append tblLink 'Append the records from a join between [Order Details] and [Products] into the
'linked Excel table
Conns.Execute "INSERT INTO disgner_begin " & strsqlfields & "values " & _
"SELECT " & strexcelfields & " from LinkedXLS" 'Detach the table and close the connection
catDB.Tables.Delete "LinkedXLS"
Set tblLink = Nothing
Set catDB = Nothing
但是我的sql server 用的是ole db 连接
excel 用的是jet oledb
所以搞不定了!!
可能真的无药可救了。害的俺白花心思。呜呜
'参阅 T-SQL 帮助: OPENROWSET() 的用法
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=test"
adoConnection.Execute "INSERT INTO Table1 (f1,f2,f3) SELECT * FROM OPENROWSET('MSDASQL','DRIVER={Microsoft Excel Driver (*.xls)};DBQ=d:\excel97.xls','select * from [test$]')"
http://go1.163.com/~askpro/msg36/qa53.htm