前幾天在 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) 人氣()


留言列表 (2)

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

    如果真的很不巧資料表中就是存在有合併儲存格,而且量還很多(EX:A1~A10,B1~B3、B4~B7、B8~B10),是否有快速還原儲存格的方式【還原後的儲存格需要填滿原始資料】。
  • 不是很確定你合併後的資料狀態,可以抓張螢幕擷圖舉個例嗎?:)

    fannys23 於 2011/07/08 18:25 回覆

  • guest
  • excel 資料表中存在有合併儲存格,如下:

    據點 組別 姓名 AMT
    據點A X組 黃洋 646
    未分組 未分組 277
    鄭福 679
    Y組 張源 167
    Z組 孟莉 262
    張娟 117
    黃豐 740
    據點B X組 吳賢 109
    李賢 466
    林萍 168
    林君 215
    邱銘 656
    Y組 劉文 123
    闕全 251
    蘇宇 193

    【還原後的儲存格需要填滿原始資料】

    據點 組別 姓名 AMT
    據點A X組 黃洋 646
    據點A 未分組 未分組 277
    據點A 未分組 鄭福 679
    據點A Y組 張源 167
    據點A Z組 孟莉 262
    據點A Z組 張娟 117
    據點A Z組 黃豐 740
    據點B X組 吳賢 109
    據點B X組 李賢 466
    據點B X組 林萍 168
    據點B X組 林君 215
    據點B X組 邱銘 656
    據點B Y組 劉文 123
    據點B Y組 闕全 251
    據點B Y組 蘇宇 193

    因為欄位還滿多的,而您所提到『* 資料表中不宜合併儲存格,以免影響排序與統計等功能的正常使用。』卻是我目前面臨最大的問題,不知是否有快速還原儲存格資料的方式。
  • 我試著搜尋一下,看起來好像沒有快速的解法耶。
    如果這樣的資料很多,我應該會寫個 VBA 來判斷,逐欄檢查,
    當後一欄有值、前一欄是空的,那麼就將上一列的資料填進前一欄的儲存格中。

    fannys23 於 2011/07/27 18:45 回覆