ADO操作excel有关问题

ADO操作excel问题

VB code

  Dim Excel_Dsn As String    Dim Excel_Conn As New ADODB.Connection    Dim Excel_Adodc As New ADODB.Recordset    Dim mySql As String    Dim rs_str  As String    Dim FilePath As String    FilePath = App.Path & "\aaa\" & Date & ".xls"   Excel_Dsn = "DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""" & FilePath & """;DBQ=" & FilePath   Excel_Conn.Open Excel_Dsn   mySql = "select * from [test$] where 网址='" & url & "'"   Excel_Adodc.Open mySql, Excel_Conn, adOpenKeyset, adLockPessimistic

以上代码在第一次打开VB工程时调试运行时会出现 ODBC驱动程序不支持的属性错误。改成
Excel_Adodc.Open mySql, Excel_Conn, 2, adLockPessimistic则是不支持的排序，有没有办法解决？

[解决办法]
http://zhidao.baidu.com/question/200819684.html
[解决办法]

VB code

im Excel_Dsn As StringDim Excel_Conn As New ADODB.ConnectionDim Excel_Adodc As New ADODB.RecordsetDim mySql As StringDim rs_str  As StringDim FilePath As StringFilePath = App.Path & "\aaa\" & Date & ".xls"Excel_Dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & FilePath & ";Extended Properties='Excel 8.0;HDR=Yes'"Excel_Conn.Open Excel_DsnmySql = "select * from [test$] where 网址='" & url & "'"Excel_Adodc.Open mySql, Excel_Conn, adOpenKeyset, adLockPessimistic
[解决办法]
参考这个：

’经实际测试，以下代码数据库和Excel之间互相导入导出，完全成功！
Private Sub Command1_Click()
   'access导出到excel
   Dim db As New ADODB.Connection
   Dim sPath As String
  
   db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Temp\Test\db1.mdb Persist Security Info=False"
  
   sPath = App.Path + "\backup.xls"
   If Dir(sPath) <> "" Then
       Kill sPath
   Else
  
   Call db.Execute("select * into Sheet1  In '" & sPath & "' 'excel 8.0;' from 表1")
       MsgBox "导出成功", vbOKOnly, "提示"
   End If
  
   db.Close
   Set db = Nothing
End Sub

Private Sub Command2_Click()
   '从excel导出到 access
  Dim db As New ADODB.Connection
   Dim sPath As String
  
   db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Temp\Test\db1.mdb;Persist Security Info=False"
  
   sPath = App.Path + "\backup.xls"
   Call db.Execute("select * into Table4 From [Sheet1$]  In '" & sPath & "' 'excel 8.0;'")
      
   db.Close
   Set db = Nothing
End Sub
 

 

查看更多 下一篇