Excel 2013: 新功能簡介1 Flash Fill

資料 >資料工具> Flash Fill
根據第1個輸入的例子, 從資料欄自動拆到其他欄位.
基本上就是半自動版本的"資料剖析"

例如下圖,有一欄位"姓名"(B欄). 現在想把姓和名分拆出來.
1. 先在 C2 和 D2分別輸入 "陳"和 "小明" (配合B2的"陳小名")

Excel: 雙擊Excel檔後,Excel程序打開了,但Excel檔沒有打開

雙擊Excel檔後時,Excel打開,但Excel檔沒有打開(Excel XP,03)
或是出現"傳送命令給程式時發生錯誤"信息(Excel 07,10).
但是,在Excel程序直接打開檔案卻沒問題.

可以試一下以下設定.

Office 2007/2010:
"檔案"->選項->進階->(一般)忽略其他使用動態資料交換(DDE)的應用程式
拿走DDE這個打勾, 確定後存一檔, 重安Excel即可.

Office 2000-03:
工具->選項->一般->忽略其他應用程式
拿走這個打勾, 確定後存一檔, 重安Excel即可.

參考 http://support.microsoft.com/kb/211494/en-us
本篇最新更新 2012-07-18

Excel:VBA 連接SQL SERVER拿取數據

以下將會介紹一下如何從SQL SERVER 的用SQL 或 Store procedure(有沒有#temp table) 拿出數據
I. 基本ADO連接
(關於User name/Password 或 Integrated 請看2.連接方法)
1. 到"工具"->"設定引用項目"
找出併打勾"Microsoft ActiveX Data Objects 2.8 Library" (2.7 也可)

Windows XP 已經預帶2.7版本(MDAC 2.7), 如果還是找不到, 可以到這個位置下載
2. 連接方法(connection)
引用好ADO後, 就可以在VBA使用ADO部件
以下是用User Name和password 連接數據庫的方法

Dim Conn As ADODB.Connection
Dim sConnect As String
Dim strSqlInstance As String
Dim strSqlDB As String
Dim strSqlUser As String
Dim strSqlPWD As String
strSqlInstance = "SERVER_NAME\INSTANCE" 'SQL SERVER實例
strSqlDB = "DATABASE NAME" '數據庫名稱
strSqlUser = "SA" 'SQL實例用戶名
strSqlPWD = "PASSWORD" 'SQL實例用戶密碼
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
…
Conn.Close

如果客戶機有設Active directory等, 可以用

sConnect = sConnect & " INTEGRATED SECURITY=sspi;"

來取代

sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"

II. 拿出資料表(table)數據 (SQL SELECT)
把數據表 Information_Schema.tables 的數據
用sql “Select * From ..." 抽出 放入 工作頁 "DATA”

Sub subGetTableValues()
 
    Dim Conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim intColCounter As Integer
    Dim sConnect As String
 

    Dim strSqlInstance As String
    Dim strSqlDB As String
    Dim strSql As String
 

    strSqlInstance = "SERVER_INSTANCE"
    strSqlDB = "database"

    sConnect = "PROVIDER=SQLOLEDB;"
    sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
    sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
   
    'Establish connection
    Set Conn = New ADODB.Connection
    strSql = "SELECT * FROM Information_Schema.tables" 'SQL在這裡定義,可加上 where group by... 
    With Conn
        .ConnectionString = sConnect
        .CursorLocation = adUseClient
        .Open
        .CommandTimeout = 0
        Set rs = .Execute(strSql)
    End With

    Worksheets("Data").Cells.Clear
    If rs.RecordCount > 0 Then '如果有數據
        '寫出欄名
        For intColCounter = 0 To rs.Fields.Count - 1
           Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
        Next
        '寫出內容 
       Worksheets("Data").Range("A2").CopyFromRecordset rs
    Else
        MsgBox ("找不到數據")
    End If
 

    rs.Close
    Conn.Close
    Set rs = Nothing
    Set Conn = Nothing
 
End Sub

另一個方法可代替CopyFromRecordset,一格一格寫上.

Application.ScreenUpdating = False '先關掉顯示,寫好再補上,可以省"點"時間
Dim intRow as long, intCol as long
intRow = 0
rs.MoveFirst
Do While rs.EOF = False
    For intCol = 0 To rs.Fields.Count - 1
        Range("A2").Offset(intRow, intCol).Value = rs.Fields(intCol).Value
    Next intCol
    rs.MoveNext
    intRow = intRow + 1
Loop
Application.ScreenUpdating = True

III. 拿出儲存程序(stored procedure, sp)結果集
假設要呼叫的SP. SP有3組參數:
1. 字串輸入: astring_input
2. 數字輸入: aint_input
3. 可變數字輸入: aint_output
最後SP會把 3組參數用1個結果集回傳出來, 和把aint_output(3)設定為999
例如, exec usp_test_vba 'abc',10,100 會回傳以下的表(第13-16行)

string input	integer input	integer output
abc	10	999

另一方面, SP的RETURN會回傳777的值 (第18行)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE usp_test_vba
@astring_input varchar(50),
@aint_input integer,
@aint_output integer output
AS

set @aint_output = 999

select 
@astring_input as [string input], 
@aint_input as [integer input], 
@aint_output as [integer output]

RETURN 777
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

呼叫方法:

Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnect As String
Dim intArgOutput As Integer
Dim intReturn As Integer
intArgOutput = 30
spName = "usp_test_vba"
Dim strSqlInstance As String
Dim strSqlDB As String
strSqlInstance = "SQL SERVER INSTANCE"
strSqlDB = "DATABASE NAME"

sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
 
Set ADODBCmd = New ADODB.Command
Conn.CommandTimeout = 0
With ADODBCmd
    .ActiveConnection = Conn
    .CommandText = spName
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters(1).Value = "abc" '放入第1個參數
    .Parameters(2).Value = 5 '放入第2個參數
    .Parameters(3).Value = intArgOutput '放入第3個參數
    Set rs = .Execute()
    intArgOutput = .Parameters(3).Value '回傳第3個參數,經SP處理後數值, 即999
    intReturn = .Parameters(0).Value '回傳SP RETURN出來的數值 即777
End With
Worksheets("Data").Cells.Clear
If Not (rs.EOF) Then
    For intColCounter = 0 To rs.Fields.Count - 1
        Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
    Next
 
   Worksheets("Data").Range("A2").CopyFromRecordset rs
Else
    MsgBox ("沒有回傳表")
End If

rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing

IV. 拿出儲存程序(SP)結果集, SP上有臨時表 (#TEMP TABLE)的問題
假設要呼叫的SP,中間有使用臨時表.
用上III方法,運行時可能會出現這樣的錯誤:
"當物件關閉時, 不允許操作"

但是直接在QA/SSMS運行是沒有問題的.
這個問題的解決方法 ,可以在SP上要加上這一行

SET NOCOUNT ON

詳情可以參考這裡. (實際上我也沒有看詳情, 為了找出這個BUG,已經花了半天來Google...)
下一篇會說一下UPDATE/DELETE/INSERT等的
本篇最新更新 2012-07-02

Excel:VBA:日期 找出某年的第1個星期天日期

本篇原自 twbts問題: 每年的第一個星期六

由於懶的關係, 在google大神搜了一下,發現這個 First Monday Of The Year (YearStart)
測試一下後,發現原是有錯的
YearStart(2014)會出現 30/12/2013...
只好自己弄一個,以下可找出在 某一年的 第一個的 某個 星期幾

Public Function YearStartShort(WhichYear As Integer, WhichWD As Integer) As Date
    Dim wd As Integer
    Dim NewYear As Date
    YearStartShort = DateSerial(WhichYear, 1, 1)
    wd = WeekDay(NewYear)
    YearStartShort= DateAdd("d", WhichWD - wd + IIf(WhichWD - wd >= 0, 0, 7), NewYear)
End Function

例如想找 2015年第1個星期五是什麼日期:
YearStartShort(2015, 6)
或
YearStartShort(2015, vbFriday)

WhichYear 為年份
WhichWD 為星期幾:
1 = 星期日 = vbSunday
2 = 星期一 = vbMonday
3 = 星期二 = vbTuesday
4 = 星期三 = vbWednesday
5 = 星期四 = vbThursday
6 = 星期五 = vbFriday
7 = 星期六 = vbSaturday


本編最後更新:2012-07-01