阿男的部落格

星期六, 七月 11, 2009

Because you loved me

for all those times you stood by me
for all the truth that you made me see
for all the joy you brought to my life
for all the wrong that you made right
for every dream you made come true
for all the love i found in you
i'll be forever thankful baby
you're the one who held me up
never let me fall
you're the one who saw me through
through it all

you were my strength when i was weak
you were my voice when i couldn't speak
you were my eyes when i couldn't see
you saw the best there was in me
lifted me up when i couldn't reach
you gave me faith 'coz you believed
i'm everything i am because you loved me

you gave me wings and made me fly
you touched my hand i could touch the sky
i lost my faith, you gave it back to me
you said no star was out of reach
you stood by me and i stood tall
i had your love i had it all
i'm grateful for each day you gave me
maybe i don't know that much
but i know this much is true
i was blessed because i was loved by you

you were my strength when i was weak
you were my voice when i couldn't speak
you were my eyes when i couldn't see
you saw the best there was in me
lifted me up when i couldn't reach
you gave me faith 'coz you believed
i'm everything i am
because you loved me

you were always there for me
the tender wind that carried me
a light in the dark shining your love into my life
you've been my inspiration
through the lies you were the truth
my world is a better place because of you
you were my strength when i was weak
you were my voice when i couldn't speak
you were my eyes when i couldn't see
you saw the best there was in me
lifted me up when i couldnt reach
you gave me faith 'coz you believed
i'm everything i am
because you loved me

星期三, 七月 08, 2009

新竹風

新竹風　
新竹風

輕輕的聽風來唱歌　
一步一步走入竹塹城
遠遠的十八尖山　
深情的南寮漁港
住在這　有風來作伴

一畷一畷一畷的米粉絲　
日頭照到春風微微
城隍廟邊　
燒燒的貢丸香味
透早到英晚　
一年過一年

你甘知道　
阮這有風　
你甘知道　
人人在說
吹動青春的日子較感動　
是阮這　
一陣陣的新竹風

輕輕的聽風來唱歌　
一步一步走入竹塹城
遠遠的十八尖山　
深情的南寮漁港
住在這　有風來作伴
住在這　有你來作伴

星期日, 七月 05, 2009

如何用Outlook規則精靈自動執行巨集?

規則精靈我想大家都很清楚了,它可以設定針對符合特定條件的郵件執行你所需要的動作。
最簡單的,就是由寄件人和主旨判斷郵件該放到哪個資料夾裡。

如果能配合巨集,那就可以擁有更強大的自動功能。
例如在我的工作上,每天都會定時收到一些報表,這些報表是以附件的形式,透過email送到我的信箱。

如果每天我都要手動去把那些報表用"另存新檔"儲存在指定的資料夾,或許甚至要把這些報表轉換成特定格式,那會是很煩人又浪費時間的一件事。

所幸,如果結合Outlook的規則精靈和巨集功能,這些事都可以讓Outlook自動完成,一旦設定好了,它就可以每天自動幫你完成這些工作。

我們假設情況是這樣的:外包廠每天寄來的mail裡,會有一些郵件的寄件人和主旨為
寄件人: support@subcon.com.tw
主旨: Summary

這個郵件會包含一個或數個附件,附件就是我們所需要的生產結果報表。

這些mail會由外包廠每天不定時傳送,在收到這樣的郵件後,我會將它的報表,也就是我們所要的生產結果,放在下面這個資料夾中:
D:\Subcon\Summary

我希望在Outlook收到這樣的郵件後,由規則精靈自動判斷,然後自動將我所要的附件儲存在指定的資料夾中。

這個部份的功能要分為兩部份來講,一是規則精靈的部份,一個是VBA的部份。

首先,當然要先完成VBA巨集,這個巨集的工作,就是把郵件中的附件儲存在D:\Subcon\Summary資料夾中。程式碼如下:

Public Sub SaveAttachment(objMsg As Outlook.MailItem)
    Dim objAttachments As Outlook.Attachments
    Dim objSelection As Outlook.Selection
    Dim i As Long
    Dim lngCount As Long
    Dim strFile As String, strExt As String
    Dim strFolderpath As String
    Dim strSaveFile As String

    strFolderpath = "D:\Subcon\"
    Set objAttachments = objMsg.Attachments
    lngCount = objAttachments.Count
    If lngCount > 0 Then
        For i = 1 To lngCount
            ' 取得附件檔名
             strFile = objAttachments.Item(i).filename
            ' 副檔名,可用來對附件來做進一步處理
            ' strExt = Right(strFile, 4)
             ' 指定儲存路徑
             strSaveFile = strFolderpath & strFile
            ' 儲存附件
             objAttachments.Item(i).SaveAsFile strSaveFile
        Next i
    End If

    '在郵件主旨上加上"SAVED!"
    objMsg.Subject = "SAVED!" + objMsg.Subject
    objMsg.Save
End Sub

這個副程式有兩個地方要注意,一是它必需是Public的,另一個是它必需以Outlook.MailItem型別為參數,這個參數就是符合規則精靈條件的郵件。

接下來是設定規則精靈,讓它在郵件送達時執行這個巨集,將附件儲存起來。設定如下:

當郵件送達後,若寄件者地址中有support@subcon.com.tw,且主旨或內文中有Summary,就執行指令碼。
而指令碼就選擇上述的SaveAttachment。

星期六, 六月 13, 2009

在VBA中搜尋檔案

1.方法1:
利用My.Computer.FileSystem.GetFiles取得符合條件的檔案, 例如:


For Each foundFile As String In My.Computer.FileSystem.GetFiles("D:\book", FileIO.SearchOption.SearchTopLevelOnly, "*.txt")
   foundFile = foundFile & vbCrLf
    My.Computer.FileSystem.WriteAllText("D:\book\FileList.txt", foundFile, True)
Next

這段程式碼會找出D:\book目錄中所有.txt檔,並記錄在D:\book\FileList.txt中.

2.方法2:
在VBA中利用Application.FileSearch搜尋, 例如:


With Application.FileSearch
    .NewSearch
    .LookIn = "D:\Temp"     
    .SearchSubFolders = True
    .Filename = "*.xls"
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
End With

設定搜尋條件,在D:\Temp中搜尋*.xls檔.


With Application.FileSearch
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & " file(s) found."
        lastfile = ""
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

列出所有找到的檔案名稱.