none
利用SQL 來讀取 Sheet 問題 RRS feed

  • 問題

  • 想請問

    把EXCEL 的Sheet 當作 DB裡面的TB 來使用
    看到的範例如 下 可以使用 select 來查詢裡面的資料
    想請問
    Q1. 用SELECT 來查  會比 用FIND 來查好嗎

    Q2.SHEHET 與SHEET 間 是否也可以拿來用 join 呢

    Q3. 若我要 將 Sheet1 INSERT 到 Sheet2
         或是  Update Sheet2
         那 要如何 撰寫呢 (因為
             myCon.Open "Provider=MSDASQL;" & myCnc   
          myRst.Open myCmd, myCon, adOpenDynamic
          的設定 不知道要如何設)


    Sub price()  
        thisfilename = ThisWorkbook.Name   
        sheetName = "temp"  
         
        Dim myCon     As New ADODB.Connection
        Dim myRst     As New ADODB.Recordset
        Dim myCnc     As String
        Dim myCmd     As String
        Dim myFileName As String
       
        myCnc = "Driver={Microsoft Excel Driver (*.xls)};" & _
                "DBQ=" & ThisWorkbook.Path & "\" & thisfilename & ";"
        '以SQL來指定讀入資料
       '--------------------------------------
            myCmd="select * from [temp$] " 
           
           
          myCon.Open "Provider=MSDASQL;" & myCnc   
          myRst.Open myCmd, myCon, adOpenDynamic
          ThisWorkbook.Sheets("XX").Range("A2").CopyFromRecordset myRst  

          myRst.Close
          Set myRst = Nothing
          myCon.Close
          Set myCon = Nothing
           
        '--------------------------------------

    End Sub

    2006年8月4日 上午 08:44

解答

  • 因為沒人回,你就將就點... 看看我的建議。

    1. 你的 Find 並沒有說清楚是哪個類別的動作,假定你是 Automation 的 Find ,若是這種情況,用 Select 會比 Find 快,而且方便,因為不用使用物件,所以使用者電腦上也不需要安裝 Excel 。但是要當成資料庫來搜尋時,本身該表格要符合資料庫的形式,且要考慮到 Excel 自動決定型別的問題。

    2. 應該可以,不過我沒做過。

    3. 早期 MDAC 2.5 用 OleDB 可以寫入(新增及更新),用 ODBC 不行,ODBC 只能讀取。據說 MDAC 2.7 以後 ODBC 也可以寫入,不過我之後沒拿 Excel 當資料庫試過,所以不確定,我可以確定 oledb 可以。

    Excel 當資料庫,同時間只能有一人存取,基本上玩玩沒啥關係,而當資料庫的 Excel ,所有的格式、公式不能當成 Automation 用,這個要注意。

    我不知道你是在 VB 還是 VB.NET 下用,若是 VB 下用,關於 ADO.NET 的部份請跳過,詳情可參考下列網址的範例:

    http://support.microsoft.com/search/default.aspx?query=ADO+Excel&catalog=LCID%3D1028&spid=&qryWt=&mode=r&cus=False

    裡面分別有 OleDB 及 ODBC 連線的範例。

    2006年8月5日 上午 10:17
    版主

所有回覆

  • 因為沒人回,你就將就點... 看看我的建議。

    1. 你的 Find 並沒有說清楚是哪個類別的動作,假定你是 Automation 的 Find ,若是這種情況,用 Select 會比 Find 快,而且方便,因為不用使用物件,所以使用者電腦上也不需要安裝 Excel 。但是要當成資料庫來搜尋時,本身該表格要符合資料庫的形式,且要考慮到 Excel 自動決定型別的問題。

    2. 應該可以,不過我沒做過。

    3. 早期 MDAC 2.5 用 OleDB 可以寫入(新增及更新),用 ODBC 不行,ODBC 只能讀取。據說 MDAC 2.7 以後 ODBC 也可以寫入,不過我之後沒拿 Excel 當資料庫試過,所以不確定,我可以確定 oledb 可以。

    Excel 當資料庫,同時間只能有一人存取,基本上玩玩沒啥關係,而當資料庫的 Excel ,所有的格式、公式不能當成 Automation 用,這個要注意。

    我不知道你是在 VB 還是 VB.NET 下用,若是 VB 下用,關於 ADO.NET 的部份請跳過,詳情可參考下列網址的範例:

    http://support.microsoft.com/search/default.aspx?query=ADO+Excel&catalog=LCID%3D1028&spid=&qryWt=&mode=r&cus=False

    裡面分別有 OleDB 及 ODBC 連線的範例。

    2006年8月5日 上午 10:17
    版主
  • 謝謝您的幫忙!

    不好意思,我再解釋一下 ,目前我是開發 Excel VBA

    1.我說的 FIND 是 EXCEL 中 ,若有一個 Sheet("CUNR") ,我若 指有一個條件,或許ㄧ般都會用  find 取找

      現在 想說使否利用 將 Sheet("CUNR") 當作一個Table ,利用Select 來找資料

      順便 ㄧ問,一班是否可以用(多個 參數 + find )來找資料呢

      還是要用其他 FUNCTION

     

    3.因為 目前要開發USER 端的程式

      因為 有些資料 DB 上,若每ㄧ筆資料都要到DB 去做ㄧ次 select ,則可能會造成網路問題

       所以 想說,USER 依據需求把相關資料 撈成不同 Sheet

       再用這些Sheet 來模擬  DB中的 Table

       這樣 網路負擔會少一點

     

     

     

    2006年8月9日 上午 01:03
  • 1.

    凡是依據 Excel 物件類別架構來執行的,不管你是使用 VB/VBA/VBScript/VBNET ,都可以說成透過 Automation ,使用 Automation ,請在 Excel 下按 Alt+F11、F1 ,來瀏覽線上說明。

    你可以手動操作進階搜尋看看,這是 Excel 內建搜尋功能的上限,也就只有這些功能而已。

    3.

    撈成檔案型的話,Access 會比較快也比較方便,另外一般可能也會考慮 XML ,用 Excel 來存通常會有特殊需求才會選用 Excel 。

    2006年8月9日 上午 02:05
    版主
  • 謝謝您幫忙
    2006年8月9日 上午 09:19
  • 這篇是用 ADO.NET 存取 Excel 的範例程式碼:

    http://support.microsoft.com/kb/316934/zh-tw

    各種基本功能都顧到了,還算滿完整的。

    2006年8月10日 上午 02:40
    版主
  • 請問  您POST 的程式碼

    VBA 也可以用嗎??

    不好意思,初學VBA ,沒用過 VB

    2006年8月17日 上午 09:38
  • ADO.NET 是 VB.NET 在用

    VBA 用的可參考這篇:

    http://support.microsoft.com/kb/257819/zh-tw

    2006年8月17日 下午 01:19
    版主
  • 謝謝您

    可是 ,這篇 重點 update ,insert ,DELETE 的用法卻沒講

    然後 我 參考了其他方式 試者 已下卸程式碼 來修改 看看 ,看是否能  做 DELETE的動作

    可是卻出現

    ---->目前的資料錄集不支援更新 ,這可能提供者 或所選定類型的限制

    另外想請教

        myRst.Delete (adAffectCurrent)  '-----裡面的參數 用意為何,在EXCEL 裡面的說明找不到 任何說明

     

    Sub DelFirstRec()

        Dim myCon     As New ADODB.Connection
        Dim myRst     As New ADODB.Recordset
        Dim myCnc     As String
        Dim myCmd     As String
        Dim myFileName As String
        Dim i         As Long

        Filename = ThisWorkbook.Name

        myCnc = "Driver={Microsoft Excel Driver (*.xls)};" & _
                "DBQ=" & ThisWorkbook.Path & "\" & Filename & ";"
           
                       
        '以SQL來指定讀入資料
       
        myCmd = "select * from [sheet2$] where A01=4 "  '範圍未知,
           
        myCon.Open "Provider=MSDASQL;" & myCnc
        'myCon.Execute myCmd,
       
       
        myRst.Open myCmd, myCon, adLockOptimistic
       
       
        'rs.Open "select * from [sheet2$] where A01=4", CurrentProject.Connection, _
                adOpenKeyset , adLockOptimistic
        myRst.MoveFirst
        myRst.Delete (adAffectCurrent)
       
        

        myCon.Close
        Set myRst = Nothing                     '物件的釋放
        Set myCon = Nothing

      
    End Sub

     

     

    2006年8月18日 上午 01:39
  • 一開始就有提到 oledb 跟 odbc 的差異。關於連線字串可參考:

    http://www.connectionstrings.com/

    摘錄 Excel 相關部分:

  •  ODBC

    •  Standard:
      "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"
      TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
  •  OLE DB

    •  Standard:
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
      "HDR=Yes;" indicates that the first row contains columnnames, not data
      "IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that this option might affect excel sheet write access negative.
      TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
      TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
      Important note! The two double quota ("") in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \") or maybe single quota (').
2006年8月18日 上午 02:46
版主