讓ADO去抓TEXT檔

Much ADO About Text Files
http://msdn.microsoft.com/en-us/library/ms974559.aspx

教你如何把Text檔當作資料庫一般，讓ADO去抓Text檔的資料。

用 EXCEL VBA QueryTables 物件向 Access 取得資料

QueryTables 物件的 ADD 方法有三個參數，分別為 Connection、Destination 與 Sql。

Microsoft 的官方說明如下：

Creates a new query table. Returns a QueryTable object that represents the new query table.
expression.Add(Connection, Destination, Sql)expression Required. An expression that returns a QueryTables object.
Connection Required Variant. The data source for the query table. Can be one of the following:

• A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;".

• A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range. Specifying a QueryTable object causes the Sql argument to be ignored.

• An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited.

• A Web query. A string in the form "URL;", where "URL;" is required but not localized and the rest of the string is used for the URL of the Web query.

• Data Finder. A string in the form "FINDER;" where "FINDER;" is required but not localized. The rest of the string is the path and file name of a Data Finder file (*.dqy or *.iqy). The file is read when the Add method is run; subsequent calls to the Connection property of the query table will return strings beginning with "ODBC;" or "URL;" as appropriate.

• A text file. A string in the form "TEXT;", where TEXT is required but not localized.

Destination Required Range. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTables object specified by expression.
Sql Optional Variant. The SQL query string to be run on the ODBC data source. This argument is optional when you're using an ODBC data source (if you don't specify it here, you should set it by using the Sql property of the query table before the table is refreshed). You cannot use this argument when a QueryTable object, text file, or ADO or DAO Recordset object is specified as the data source.

先前，我都是用 ADO 物件向 Access 索取資料。
每次使用 ADO 總是要先宣告許多 ADODB 變數，如： Connection 、 Recordset 等，先建立 Connection 然後再用 Execute 方法把資料送給 Recordset ，再用 EXCEL 的 CopyFromRecordset 把資料塞到儲存格中，每次程式碼都寫得很長，造成閱讀或維護的困難。

前幾天在 MSDN 上查閱 QueryTables 物件的說明，其中這句「A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;".」讓我萌生用 QueryTables 向  Access 索取資料的想法。

我嘗試以 EXCEL 2010 向  Access 2003 所建立的 MDB 檔案索取資料，試驗的結果非常成功。不論用 ODBC 還是 OLE DB 都能夠順利取得資料。
更棒的是，可以用 QueryTables 物件的 SQL 參數，以 SQL 語法取得想要的資料。
程式碼如下：
ODBC 法：

With ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\myDB.mdb;uid=Admin;Pwd=", _
    Destination:=Sheet1.Range("A1"), _
    Sql:="SELECT * FROM Tbl_l Where Col_1=x")
        .Refresh
End With

OLE DB 法：

With ActiveSheet.QueryTables.Add( _
    Connection:="OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDB.mdb;User Id=Admin;Password=, _
    Destination:=Range("A1"), _
    Sql:="SELECT * FROM Tbl_l Where Col_1=x") 
        .Refresh
End With

P.S. 1 其中 Connection 參數的語法為： ODBC; 或 OLEDB;。
P.S. 2 其中 根據資料庫類型 (SQL Server, Access, Oracle) 的不同而有不同的指令，可以去 ConnectionStrings.Com 查。

2012/5/14 Update:
很遺憾的，QueryTables 物件 ADD 方法的 Sql 參數無法接受「參數查詢」。但是可以用 VBA 結合字串的方式解決。

[ADOX]印出查詢中的SQL指令

Sub test()
    Dim objConn As Object, objCat As Object, Prc As Object, i As Long
   
    Set objConn = CreateObject("adodb.connection")
    Set objCat = CreateObject("adox.catalog")
   
    With objConn
        .connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myPath\泛myAccess.mdb"
        .Open
        If .State <> 1 Then Exit Sub
    End With
    On Error GoTo errHandle
    With objCat
        .activeconnection = objConn
        i = 1
        For Each Prc In objCat.procedures
            Cells(i, 1).Value = Prc.Name
            Cells(i, 2).Value = Prc.Command.CommandText
            i = i + 1
        Next
    End With
    objConn.Close
    Exit Sub
errHandle:
    objConn.Close
   
End Sub

[ADOX]查詢資料庫中資料表之名稱

要查詢或定義資料庫的內部結構(例如：欄位、主鍵、table name、view name等)，就要用ADOX物件。
要使用ADOX之前，要先用ADO建立連結，之後才能使用ADOX物件。

Dim objConn as object, objCat as object, objTbl as object

set objConn=createobjcet("ADODB.connection")
set objCat=createobjcet("ADOX.catalog")
set objTbl=createobjcet("ADOX.table")

with objConn
.connectionstring="Provider="Microsoft.Jet.OLEDB.4.0; data source=mypath/myAccessFile.mdb"
.open
end with

with objCat
.activeconnection=objconn
for each objTbl in objCat.tables
debug.print objTbl.name
next
end with

For-Each與For-Next何者較快？

如果二者都可以達到相同的目的，則For-Each陳述句似乎會比較快！

Sub test_For_each()
    Dim RowNum As Long, ColNum As Long, time1 As Double
    Dim c As Range, i As Long
   
    Cells.Clear
    RowNum = InputBox("幾列？")
    i = 0
    time1 = Timer
   
    For Each c In Range("A1").Resize(RowNum, 1)
        i = i + 1
        c.Value = i
    Next c
   
    MsgBox Format(Timer - time1, "0.00") & " secs."
   
End Sub

==================================================================

Sub test_For_next()
    Dim RowNum As Long, ColNum As Long, time1 As Double
    Dim c As Range, i As Long, j As Long, k As Long
   
    Cells.Clear
    RowNum = InputBox("幾列？")
    k = 0
    time1 = Timer
   
    For i = 1 To RowNum
            k = k + 1
            Range("A1").Offset(i - 1, 0).Value = k
    Next i
   
    MsgBox Format(Timer - time1, "0.00") & " secs."
   
End Sub

以陣列變數寫入EXCEL 儲存格範圍可節省運算時間

這幾天重新翻閱《Excel VBA 徹底研究》這本書。
書中第11章提到，將資料逐一寫入儲存格範圍是非常消耗時間的，建議改用陣列變數將數值寫入儲存格範圍。

以下以傳統方式跑100*100的範圍，花了3.18秒。

Sub LoopFillRange()

    Dim cellsDown As Long, cellsAcross As Integer

    Dim CurrRow As Long, CurrCol As Integer

    Dim StartTime As Double

    Dim CurrVal As Long

    

    Cells.Clear

    cellsDown = Val(InputBox("How many cells down?"))

    cellsAcross = Val(InputBox("How many cells across?"))

    

    StartTime = Timer

    

    CurrVal = 1

    

    Application.ScreenUpdating = False

    

    For CurrRow = 1 To cellsDown

        For CurrCol = 1 To cellsAcross

            ActiveCell.Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal

            CurrVal = CurrVal + 1

        Next CurrCol

    Next CurrRow

    

    Application.ScreenUpdating = True

    

    MsgBox Format(Timer - StartTime, "00.00") & "秒"

End Sub

以下以陣列變數的模式，跑100*100的儲存格僅花了 0.05秒。
重點在於我用橘色框起來的 theRange.Value = tmpArray 這段敘述。

Sub ArrayFillRange()

    Dim cellsDown As Long, cellsAcross As Long

    Dim i As Long, j As Long

    Dim CurrRow As Long, CurrCol As Integer

    Dim StartTime As Double

    Dim CurrVal As Long

    Dim tmpArray() As Long

    Dim theRange As Range

    

    Cells.Clear

    cellsDown = Val(InputBox("How many cells down?"))

    cellsAcross = Val(InputBox("How many cells across?"))

    

    StartTime = Timer

    ReDim tmpArray(1 To cellsDown, 1 To cellsAcross)

    Set theRange = ActiveCell.Range(Cells(1, 1), Cells(cellsDown, cellsAcross))

    CurrVal = 0

    

    Application.ScreenUpdating = False

    

    For i = 1 To cellsDown

        For j = 1 To cellsAcross

            tmpArray(i, j) = CurrVal + 1

            CurrVal = CurrVal + 1

        Next j

    Next i

    

    theRange.Value = tmpArray

    Application.ScreenUpdating = True

    

    MsgBox Format(Timer - StartTime, "00.00") & "秒"

End Sub

解決OFFICE 2007之VBA不支援Application.Filesearch

Office 2007的VBA不再支援Application.Filesearch
讓我在Office 2003上寫的VBA在移轉至Office 2007時，產生莫大困擾。

之後決定用VB的 Dir函數來取代
原碼：

With Application.FileSearch
        .LookIn = sPaht
        .Filename = sDate & "*.TAB"
        Select Case .Execute
        Case Is > 1
            esc = 1
            MsgBox "檔案有重複！" & Chr(10) & "請確認路徑 " &  .LookIn, vbCritical
        Case 0
            esc = 1
            MsgBox "找不到檔案！" & Chr(10) & "請確認路徑 " &  .LookIn, vbCritical
        Case Else
            sFilename = .FoundFiles(1)
        End Select
End With

以Dir改寫：

        If Dir(sPath & "\" & sDate & "*.TAB") <> "" Then
            If Dir <> "" Then
                esc = 1
                MsgBox "檔案有重複！" & Chr(10) & "請確認路徑 " & sPath, vbCritical
            Else
                sFilename = sPath & "\" & Dir(sPath & "\" & sDate & "*.TAB")
            End If
        Else
            esc = 1
            MsgBox "找不到檔案！" & Chr(10) & "請確認路徑 " & sPath, vbCritical
        End If
    End With

[VBA][ADO]如何設定資料庫聯結(Connection)

在ADO中常常需要設定資料庫聯結，以便存取、修改資料庫的資料。
但是，每次都要打很多文字來設定Connection屬性，有點麻煩。

最近才知道可以利用UDL檔預先設定好這些屬性。先記在這裡，以免忘記。
建立及設定UDL檔的方式：
http://msdn.microsoft.com/zh-tw/library/aa301691(v=VS.71).aspx
在ADO中引用UDL檔的方式：
http://msdn.microsoft.com/zh-tw/library/aa301678(v=VS.71).aspx

[EXCEL][VBA]EXCEL的按鈕圖示

EXCEL裡面到底提供哪些按鈕圖示呢？
根據旗標出版社所出版的「超圖解 EXCEL VBA應用講座」，EXCEL 2003中共有 7999種按鈕圖示。分別如下：