網路城邦
上一篇 回創作列表 下一篇   字體:
[Excel VBA] 大量字串取代的做法
2009/04/02 12:01:20瀏覽35425|回應2|推薦1

(本篇圖文及程式於2022年2月更新)

2009年應朋友要求寫了 Word VBA 利用Find/Replace功能做大量字串的取代(參見「如何用find/replace做大量不同字串的取代?」),後來再寫了這一篇示範 Excel VBA的寫法。最近有網友來信詢問本文程式執行時的問題,才發現貼在部落格裡的程式碼貼上之後被弄亂(UDN部落格有這個問題),於是利用休假空閒時間,將程式稍為改寫,圖文也都重做。

修改文章

記得那位朋友說,需要把一些 Word 文件檔裡面的 "第一章" 全部改成  "第1章"、 "第一款" 全部改成  "第1款"、......諸如此類,字串非常多,最多會到第一百多章、第一百多款 (可能是討論法規) 。使用 Excel 實務會遇到的情況,例如:為了資料一致性,需要把地址資料裡的 "1段"、"2段" ...... 改成 "一段"、"二段" ... ...。

要做大量取代的地址資料

我們可以使用 Windows 的 " 記事本"(有 64KB 的限制) 或 "Wordpad",建立一個取代字串的對照文字檔,另存新檔,編碼選擇 ANSI (副檔名 txt ),像這樣:

然後按 Alt+F11 進入 VBA 編輯狀態,在這個活頁簿裡面 "插入"-> "模組",Excel 會將它命名為module1,把本篇文章稍後將列出來的程式碼 "複製-貼上" 到 module1 裡面。

再按一次 Alt+F11 回到 Excel 工作表畫面,按 Alt+F8,選擇 MassReplace 巨集,"執行" 它:

執行MassReplace 巨集

這個巨集程式首先會顯示對話視窗,讓我們選擇取代字串的對照文字檔,此時就要選擇剛

才建立的文字檔:

選擇要開啟的文字檔案

按下 "開啟",片刻後,就可以看到檔案裡面字串取代的結果。

底下是把股號改成股名的例子:

[程式碼(文字)]

Option Base 0
Option Explicit
Sub MassReplace()
Dim arrStr() As String, InputStr As String
Dim sFilePath As Variant
Dim Fn As Integer
Fn = FreeFile() 取得一個可使用的檔案代號 (File Handle Number)
  顯示Windows的選擇檔案的對話窗
sFilePath = Application.GetOpenFilename("Text Files (*.txt), *.txt", 1, "選擇文字檔案", "開啟", False)
On Error Resume Next
If sFilePath Then                   若文字檔選擇成功
    Open sFilePath For Input As #Fn 開啟 Replace.txt 檔
    Application.ScreenUpdating = False 畫面暫停更新
    
    While Not EOF(Fn)
        Line Input #Fn, InputStr  從檔案讀出一列,
        If Len(InputStr) > 0 Then 略過無字串的空行
            arrStr = Split(InputStr, ",") 把讀入的文字列依逗號分成兩個字串, 置於 arrStr 陣列裡
            Call ReplaceText(arrStr(0), arrStr(1)) 叫用 ReplaceText函式, 把兩個字串傳給它執行取代
        End If
    Wend
    
    Application.ScreenUpdating = True 畫面恢復更新
    Close #Fn  關閉文字檔
Else
     MsgBox ("檔案選擇取消或失敗,將不執行文字取代。")
End If
End Sub
Function ReplaceText(Src As String, Rpl As String)
這個函式會在整個工作表裡搜尋 Src 字串, 將它取代為 Rpl 字串
Cells.Replace What:=Src, Replacement:=Rpl, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
底下是 Replace 的參數說明: What 必選的參數。要尋找的字串。
Replacement 必選的參數。要置換的字串。
LookAt 選擇性的參數。是否需全字相同。可為下列 XlLookAt 常數之一:xlWhole 或 xlPart。
SearchOrder 選擇性的參數。搜尋的順序。可為下列 XlSearchOrder 常數之一:xlByRows 或 xlByColumns。
MatchCase 選擇性的參數。若指定為 True,則搜尋時大小寫視為相異。
SearchFormat 選擇性的參數。是否依據 "格式" 搜尋。True/False布林值。(Mac版無此參數)
ReplaceFormat 選擇性的參數。是否取代格式。True/False布林值。(Mac版無此參數)
End Function

[程式碼(截圖)]

*若要索取本文的 VBA 程式碼,來信主旨請寫:「Excel 大量字串取代 VBA」
  email 請寄way2cheng@gmail.com
*相關文章:
[Word VBA]如何用findeplace做大量不同字串的取代?

( 興趣嗜好電腦3C )
回應 推薦文章 列印 加入我的文摘
上一篇 回創作列表 下一篇

引用
引用網址:https://classic-blog.udn.com/article/trackback.jsp?uid=WayCheng&aid=2808290

 回應文章

betty
請幫忙
2010/09/30 17:12

大大想問一下

為什麼我都key進去了,但出現找到到指名引數?

Function ReplaceText(Src As String, Rpl As String)

ThisIsTheWay(WayCheng) 於 2010-10-04 08:37 回覆:
你的程式裡可能有Key錯, 請檢查

Call Replace(.......) 這一行括弧裡的東西有沒有key錯

若仍找不出問題, 請把你 key 的程式碼 copy & paste 到

email 裡寄給我:

way_kkiimmoo@yahoo.com.tw

電腦搞不定
如何改寫模組!
2009/08/28 15:28

您好:

我在工作上正傷透腦筋後求助很多周遭的人,經由介紹您的網頁教學,得知有"大量字串取代的做法"

測試成功,非常好用,但是有個問題請教你,想讓"取代"功能更理想

Excel裡如果不要整個工作表做字串取代,我只要其中儲存格F行,由上至最下取代

如何改寫模組呢? 謝謝!

還有我碰到更複雜的難題就是...我能傳E-maul Excel檔範例給你看嗎? 謝謝!

ThisIsTheWay(WayCheng) 於 2009-08-30 20:51 回覆:
在程式裡先選取 F 欄:

Columns("F:F").Select

然後才 call ReplaceText()

在 ReplaceText 裡,把 Cells.Replace ......... 改成 Selection.Replace ..........

就會只針對 F 欄做取代。

作家簡介裡有我的email信箱,只是我近來事情很多很忙,身體很累,

所以會較慢回覆,先請原諒。