讓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

在Internet Explorer 中開啟 Office 檔案時出現檔案下載對話方塊

當用IE點選 Microsoft Office的超連結時，經常會出現一個對話視窗，要求使用者選擇是要開啟，或者是存檔。

以下方法可以使這個對話視窗不再出現：
For Windows 2000、Windows XP
Step 1. 我的電腦→工具(T)→資料夾選項(O)。
Step 2. 按一下「檔案類型」索引標籤，在「註冊的檔案類型」方塊中按一下特定的檔案類型(例如 XLS)，然後按一下「進階」。
Step 3. 取消「下載之後進行開啟確認」核取方塊，然後按一下「確定」

可以參考：http://support.microsoft.com/kb/303475/zh-tw#appliesto

另外，若要將其恢復，除了利用上述方法之外，還可以利用 regedit的方法：

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Policies\Microsoft\Internet Explorer\Restrictions]"AlwaysPromptWhenDownload"=dword:00000001

把這段內容存成 1.reg，然後在檔案總管中 double-click它，再回答YES即可。

EXCEL陣列公式之相關限制

最近在改寫EXCEL的Layout，將某個資料的表達由橫式轉為直式，
並將原本引用整列(例如： 1:1)的陣列公式轉為整欄(例如 A:A)。

但不論如何嘗試，EXCEL總是出現錯誤值。

經過查詢後，才知道EXCEL的陣列公式有以下限制：

1. 記憶體(DRAM)是否足夠。
2. 整欄規則：儘管EXCEL可以建立超大陣列，但不能建立使用一整欄儲存格的陣列。Microsoft所給的理由是重新計算使用整欄儲存格的陣列公式非常耗時，故EXCEL不允許在公式中建立此類陣列。
3. 陣列公式上限：一張工作表最多可包含 65,472個其他工作表的陣列公式。

資料來源：http://support.microsoft.com/kb/166342/zh-tw#appliesto

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

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

[EXCEL] GET.CELL函數

聽說GET.CELL是EXCEL 97的函數，但是在後來的EXCEL版本中被拿掉了。
我覺得它可以提供活頁簿、工作表與儲存格資訊，而且不用自己寫巨集(Macro)。
雖然現在使用起來有點麻煩，但還是先記起來，以免以後忘記了。

GET.CELL(type_num,reference)

type_num 指定要獲得之資訊的代碼
1 參照儲存格的絕對位址
2 參照儲存格的列號
3 參照儲存格的欄號
4 類似 TYPE 函數
5 參照位址的內容
6 文字顯示參照位址的公式
7 參照位址的格式，文字顯示
8 文字顯示參照位址的格式
9 傳回儲存格外框左方樣式，數位顯示
10 傳回儲存格外框右方樣式，數位顯示
11 傳回儲存格外框方上樣式，數位顯示
12 如果儲存格被設定 locked傳回 True
15 如果公式處於隱藏狀態傳回 True
16 傳回儲存格寬度
17 以點為單位傳回儲存格高度
18 字型名稱
19 以點為單位傳回字型大小
20 如果儲存格所有或第一個字元為加粗傳回 True
21 如果儲存格所有或第一個字元為斜體傳回 True
22 如果儲存格所有或第一個字元為單底線傳回True
23 如果儲存格所有或第一個字元字型中間加了一條水平線傳回 True
24 傳回儲存格第一個字元色彩數位， 1 至 56。如果設定為自動，傳回 0
25 MS Excel不支援大綱格式
26 MS Excel不支援陰影格式
27 數位顯示手動插入的分頁線設定
28 大綱的列層次
29 大綱的欄層次
30 如果範圍為大綱的摘要列則為 True
31 如果範圍為大綱的摘要欄則為 True
32 顯示活頁簿和工作表名稱
33 如果儲存格格式為多行文字則為 True
34 傳回儲存格外框左方色彩，數位顯示。如果設定為自動，傳回 0
35 傳回儲存格外框右方色彩，數位顯示。如果設定為自動，傳回 0
36 傳回儲存格外框上方色彩，數位顯示。如果設定為自動，傳回 0
37 傳回儲存格外框下方色彩，數位顯示。如果設定為自動，傳回 0
38 傳回儲存格前景陰影色彩，數位顯示。如果設定為自動，傳回 0
39 傳回儲存格背影陰影色彩，數位顯示。如果設定為自動，傳回 0
40 文字顯示儲存格樣式
41 傳回參照地址的原始公式
42 以點為單位傳回使用中視窗左方至儲存格左方水平距離
43 以點為單位傳回使用中視窗上方至儲存格上方垂直距離
44 以點為單位傳回使用中視窗左方至儲存格右方水平距離
45 以點為單位傳回使用中視窗上方至儲存格下方垂直距離
46 如果儲存格有插入批註傳回 True
47 如果儲存格有插入聲音提示傳回 True
48 如果儲存格有插入公式傳回 True
49 如果儲存格是陣列公式的範圍傳回 True
50 傳回儲存格垂直對齊，數位顯示
51 傳回儲存格垂直方向，數位顯示
52 傳回儲存格首碼字元
53 文字顯示傳回儲存格顯示內容
54 傳回儲存格樞紐分析表名稱
55 傳回儲存格在樞紐分析表的位置
56 樞紐分析
57 如果儲存格所有或第一個字元為上標傳回True
58 文字顯示傳回儲存格所有或第一個字元字型樣式
59 傳回儲存格底線樣式，數位顯示
60 如果儲存格所有或第一個字元為下標傳回True
61 樞紐分析
62 顯示活頁簿和工作表名稱
63 傳回儲存格的填滿色彩
64 傳回圖樣前景色彩
65 樞紐分析
66 顯示活頁簿名稱
reference 一個儲存格或範圍。若省略則為activecell。

使用方式：
1.在工作表中任何地方，選插入→名稱→定義。
2.在「現有名稱：」輸入任意名稱。例如：AA。
3.在「參照到：」輸入「=GET.CELL(62,sheet1!$A$1)
4.按「新增」，之後按「確定」
5.在工作表的B1儲存格中，輸入「=AA」，再按ENTER鍵，則B1儲存格即可顯現活頁簿名稱與工作表名稱。