前幾天在 msn 上接到一個小需求,
希望可以找個辦法把 Excel 上輸入的資料轉換為指定的純文字格式。
資料在 Excel 中的內容大概是長成這樣:
 

表類 股票代碼 年份 季別 會計代碼 數字A 數字B
A01 6271 96 02 1xxx 2330244 1761202


轉出來的內容很簡單,
就是把欄名拿掉、內容轉存成兩段文字,以兩個半形空白分隔,
其中數字A與數字B必須判斷是正數或負數、補上正負號,
並補齊為15位數的數字,不足的位數就補零。
最後再補上一整串固定長度的A。

 

 

所以得到的結果大概要長成這樣:
A01627196021xxx  +000000002330244+000000001761202AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

他覺得這應該很簡單、但是他不會寫,
我也覺得應該很簡單,第一個想到的解決方案就是用 python 寫,
不過被回絕了:「最好可以在不要離開 excel 視窗的前提下就可以完成這件事。」

所以這是我的第一支 VBA。
程式碼如下,有書輔助的情況下,
加上翻書的時間,不到十五分鐘就收工了。



Sub Trans()
    myDir = ThisWorkbook.Path '指定路徑為工作表所在目錄
    ChDrive myDir
    ChDir myDir
    Set myRng = Range("A1").CurrentRegion '定義要抓取的範圍
    
    Dim Cell1 As String
    Dim Cell2 As String
    Dim padding1 As String
    Dim padding2 As String
    Dim padding3 As String
    '每一行最後都有一串活潑的A(用途不明,是開需求的人指定的)
    padding3 = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
    
    Dim filename As String
    '定義檔名為時間
    filename = Format(Now(), "yyyymmddhhmmss") & ".txt"
    '開啟檔案寫入,若路徑中未有該檔案會create一個
    Open filename For Output As #1
    '第一列不抓進來,一直取到最後一列
    For i = 2 To myRng.Rows.Count
        '第一個區段: 表類、股票代碼、年份、季別、會計代碼
        Cell1 = myRng.Cells(i, 1).Text + myRng.Cells(i, 2).Text + myRng.Cells(i, 3).Text + myRng.Cells(i, 4).Text + myRng.Cells(i, 5).Text
        
        '第二個區段: 數字,要補齊 padding
        padding1 = myRng.Cells(i, 6).Text
        padding2 = myRng.Cells(i, 7).Text
        '把數字轉換為15位數數字(不含正負號)
        padding1 = LstrFix(padding1, 15)
        padding2 = LstrFix(padding2, 15)
        '與前一段文字中間空兩格半形空格
        Cell2 = "  " & padding1 & padding2 & padding3
        
        Print #1, Cell1;                                        '加分號表示不換行
        Print #1, Cell2
    Next
    Close #1
    MsgBox ("存檔成功! ")
End Sub

'拿來做數字A & B padding用的函式
Function LstrFix(myData, myLen) As String
    Dim myPad As Integer, myText As String, myPN, newData
    If Mid(myData, 1, 1) = "-" Then
        myPN = "-"                                           '處理數字開頭應出現的正負號
        myData = Mid(myData, 2, myLen)  '負數時取出數字本身(不取負號)
    Else
        myPN = "+"
    End If
        
    '設定要補齊的個數的0
    myPad = myLen - Len(myData)
    For i = 1 To myPad
        myText = myText + "0"
    Next
    
    '最後要送至檔案中的數值:正負號 + 用來補滿15位數的0 + 數字本身
    LstrFix = myPN & myText & myData
End Function



------------------------
寫這個小玩意時看的書:
古川順平,《新 Excel VBA 與資料庫整合實務講座》,博碩,2006。

裡面先教了一些 Excel 的技巧、才開始講 VBA,
可以一次滿足學習 Excel 與 VBA 的願望。

不過我後來還看了這本:
John WalkenBach, "Excel 公式函數與 VBA 整合徹底研究", 博碩, 2006.
好像也是這樣編排……

好處是古川順平這本因為是日本人寫的,所以有介紹一些遠東版才有的函式。
我看到的是全半形轉換,可以使用 StrConv 函式,設定不同的 conversion:
    StrConv(String, vbWide)      '將半形轉換為全形
    StrConv(String, vbNarrow)   '將全形轉換為半形
另一本 John WalkenBach 寫的就沒有翻到囉。

兩本寫得都很淺白,目錄也很方便查閱,
推薦給需要寫 VBA 但是平常又很少寫程式的商科學生~

------------------------
翻書時做的一點小筆記:

* 可以經由快速鍵 Ctrl-Shift-*(8) 找出資料表範圍。

* 資料表中不宜合併儲存格,以免影響排序與統計等功能的正常使用。

* 使用 VBE (Visual Basic Editor) 的方法:
    工具→巨集→Visual Basic編輯器

* 快速鍵:
    Ctrl-J 代表儲存格內的換行符號

* Excel 中不會區分文字大小寫判定,
因此要正確判斷大小寫差異,必須使用 EXACT 函數,
例如:=EXACT(LOWER(A1),A1)

* Excel 預設以筆劃排序中文資料,
若筆劃相同,則以內碼值作為排序依據。

* 為了避免 Excel 當掉,若想要叫出「尋找及取代」對話方塊,
建議不要使用該對話方塊的 show 方法:
    Applicaiton.Dialogs(xlDialogFormulafind).Show
而是改用快速鍵呼叫:
    Application.SendKeys("^f")

創作者介紹
創作者 fannys23 的頭像
fannys23

小攻城師的戰場筆記

fannys23 發表在 痞客邦 留言(2) 人氣()