×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

You must have correct connection string. such as..

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\somepath\mySpreadsheet.xls;" & _
"DefaultDir=c:\somepath"

check http://www.able-consulting.com/ADO_Conn.htm for details
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 请教:把EXCEL做DATABASE,用ODBC,在VB中怎样写SQL语句来提取数据:SELECT * FROM ??????或能不能这样做?谢谢!
    • Never tried, don't think so.
      • 谢谢!那奇怪为什么在ODBC中又允许EXCEL做数据源?
        • Sorry, it is ok, just tested.
          • How?
    • ADO
      • yes, i'm using ADO. just don't know the syntax of SQL for EXCEL. Thanks.
    • 可以。。sheet name就是表名
      • 对了,sheetname后面要加个!还是 $,忘了。。。
        • Not working. Thanks.
    • Here is how:
      insert MS DAO from Reference, name the column your want to select (named range) as, for example, "mycol"

      Dim db as DAO.Database
      Dim rs as DAO.Recordset

      set db = OpenDatabase("myfile.xls", , "Excel 8.0")
      set rs = db.OpenRecordset("SELECT * from mycol ")
      ...
      • Thanks. It doesn't work. Error: unrecognized database format.
        • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic4.asp
          Database String
          dBASE III dBASE III;
          dBASE IV dBASE IV;
          dBASE 5 dBASE 5.0;
          Paradox 3.x Paradox 3.x;
          Paradox 4.x Paradox 4.x;
          Paradox 5.x Paradox 5.x;
          Excel 3.0 Excel 3.0;
          Excel 4.0 Excel 4.0;
          Excel 5.0/Excel 95 Excel 5.0;
          Excel 97 Excel 97;
          Excel 2000 Excel 8.0;
          HTML Import HTML Import;
          HTML Export HTML Export;
          Text Text;
          ODBC ODBC;
          DATABASE=database;
          UID=user;
          PWD=password;
          DSN=datasourcename;
    • Ah, at last, I made it. The syntax is: "select * from [sheetname$]". Many thanks to everyone!
    • You must have correct connection string. such as..
      oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
      "DriverId=790;" & _
      "Dbq=c:\somepath\mySpreadsheet.xls;" & _
      "DefaultDir=c:\somepath"

      check http://www.able-consulting.com/ADO_Conn.htm for details
    • 选择excel中你需要的数据,第一行应该是属姓名,后面是值,然后insert-->name-->define-->输入一个名字,这个名字就是select * from 中的table的名字。
    • Why ADO?!, To use the Excel object is very simple and straight. (1) Declare a veriable (2) Set a value, Done.