EXCEL VBA 如何檢查工作表(名稱)是否存在

excel vba 檢查工作表(名稱)是否存在

jasonh 2019-03-21 22:58:05 ‧ 12880 瀏覽

分享至

**一EXCEL檔案於G欄有此檔案的所有工作表名稱
要一一開啟執行相同作業
若此工作表不存在
需新增此工作表於最後
試寫了一段
但不知如何寫判斷式
敬請各位大大幫忙
Sub 開工作表() '工作表名稱為文字若無此工作表要新增於最後
Worksheets("AA").Select
Rcnt = Range("G6").End(xlDown).Row '工作表名稱放於 G7 以下
For i = 7 To Rcnt
ShtN = Worksheets("AA").Range("G" & i).Value
檢查有無此名稱之工作表
若有
Worksheets(ShtN).Select
'other programs
若無
Worksheets.Add '要新增於最後
ActiveSheet.Name = ShtN
'other programs
Next i
End Sub
**

看更多先前的討論...收起先前的討論...

jasonh iT邦新手 5 級 ‧ 2019-03-22 19:21:30 檢舉

請問如何分辨英文大小寫不同的工作表名稱
如已有"A"工作表但G欄名稱為"a"
於判定時A<>a
但新增工作表名稱卻會失敗
要如何寫判定式
使大小寫英文視為相同(不一定是1碼, 有2~5碼英文字母)

paicheng0111 iT邦大師 5 級 ‧ 2019-03-22 23:42:23 檢舉

兩邊都先用LCase函數轉成小寫英文，然後再來比較。

LCase("A") = LCase("a") 為True

jasonh iT邦新手 5 級 ‧ 2019-03-23 09:10:17 檢舉

請問 pcw 以下這段
For Each sht In Worksheets
shtNames = "/" & sht.Name
Next sht
由區域變數視窗看 shtNames 只會呈現最新的工作表名稱而非所有
因此往下執行時 shtNames =最後一工作表名稱
是否可更改為
shtNames = ""
For Each sht In Worksheets
shtNames = "/" & sht.Name & shtNames
Next sht
另
發現 Match函數是不分英文大小寫的

paicheng0111 iT邦大師 5 級 ‧ 2019-03-23 11:22:16 檢舉

對不起，我打太快了。
shtNames = "/" & sht.name
應該改為
shtNames = shtNames & "/" & sht.name

已經重新編輯過答案了。

另外，Match函數確實是不分大小寫的。
一般來說，微軟的產品(windows, office等)大多是case insensitive。

最後，回應我的答案，請寫在我的回答底下，這樣系統才會通知我。

jasonh iT邦新手 5 級 ‧ 2019-03-24 19:54:47 檢舉

不好意思, 又有一些不懂得
請問 ActiveWorkbook.Sheets(WorksheetName).Name
是判定工作表的名稱(WorksheetName) 是否存在嗎
若存在回傳工作表的名稱
若不存在回傳錯誤訊號, 所以要在上方加 On Error Resume Next
但是 <>"" 又是何意義
還有為何最後又要加 On Error GoTo 0
'(這是回 On Error Resume Next--第一列? 還是???)

paicheng0111 iT邦大師 5 級 ‧ 2019-03-24 23:42:02 檢舉

<>""只是要傳回boolean值
ON error goto 0會把強制把錯誤(如果有的話)歸零

jasonh iT邦新手 5 級 ‧ 2019-03-25 18:25:21 檢舉

若有錯誤會歸零
請問何謂歸零
是忽略然後執行下一列
還是.....
與 On Error Resume Next 有何不同

paicheng0111 iT邦大師 5 級 ‧ 2019-03-25 21:34:51 檢舉

當錯誤發生時，VB會拋出一個err物件。
我自己常會去看err.number與err.description。

on error goto 0會把err物件消滅。(因為goto 0，所以我都說是歸零)
on error resume next會忽視err物件的出現，繼續執行下一行
可參考
https://docs.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/on-error-statement

jasonh iT邦新手 5 級 ‧ 2019-03-26 16:40:27 檢舉

原來 on error goto 0 是
去除(停止) On Error Resume Next 功能
以防止後續程式若有錯誤無法被找出
對吧

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

paicheng0111

iT邦大師 5 級 ‧ 2019-03-21 23:32:31

最佳解答

Sub check_sht_names()
    dim shtNames as string, shtArray as variant
    dim sht as worksheet, pos as variant
    ' get all names of worksheets
    for each sht in worksheets
        shtNames = shtNames & "/" & sht.name
    next sht
    shtNames = Mid(shtNames, 2)
    ' make a list of worksheet names
    shtArray = Split(shtNames, "/")
    
    Rcnt = Worksheets("AA").Range("G6").End(xlDown).Row
    For i = 7 To Rcnt
        ShtN = Worksheets("AA").Range("G" & i).Value
        pos = Application.Match(ShtN, shtArray, 0)
        If IsError(pos) Then
            With Worksheets.Add(after:=worksheets(worksheets.count))
                .Name = ShtN
            End With
        end if
    Next i
End Sub

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

paicheng0111 iT邦大師 5 級 ‧ 2019-03-23 11:45:09 檢舉

對不起，我打太快了。
shtNames = "/" & sht.name
應該改為
shtNames = shtNames & "/" & sht.name

已經重新編輯過答案了。

另外，Match函數確實是不分大小寫的。
一般來說，微軟的產品(Windows, Office等)大多是case insensitive。

jasonh iT邦新手 5 級 ‧ 2019-03-24 19:26:20 檢舉

shtNames = shtNames & "/" & sht.name
shtNames = "/" & sht.Name & shtNames
2個都可以
差異為一個由前到後及另一個由後到前

jasonh iT邦新手 5 級 ‧ 2019-03-24 20:26:41 檢舉

請問能否將所有工作表名稱直接逐一加入陣列(用Join或Item)
而無須先合併成一字串再一一分開成陣列
只是 Join 或 Item 都不太會用

paicheng0111 iT邦大師 5 級 ‧ 2019-03-24 23:59:24 檢舉

shtNames = shtNames & "/" & sht.name
shtNames = "/" & sht.Name & shtNames
2個都可以

確實如此

請問能否將所有工作表名稱直接逐一加入陣列(用Join或Item)
而無須先合併成一字串再一一分開成陣列

可以，但要先宣告一個不定陣列，然後逐漸加上去。大概會類似

dim ary as variant, sht as worksheet

for each sht in worksheets
    if isarray(ary) then
        redim preserve ary(ubound(ary)+1) as variant
    else
        redim ary(0) as variant
    end if
    ary(ubound(ary)) = sht.name
next sht

jasonh iT邦新手 5 級 ‧ 2019-03-25 18:23:42 檢舉

感覺用ubound一直加大陣列好像不是很有效率
(ubound 還沒用過...)
直接 Worksheets.Count(cnt) 再加 ReDim陣列(1 to cnt)
再用 for(1 to cnt) next... 將現有每一工作表名稱加入陣列
如此是否會比較有效率(當現有工作表很多(50~60)時)

paicheng0111 iT邦大師 5 級 ‧ 2019-03-25 21:43:06 檢舉

當然可以。
可能會更好吧。(我沒有測試過)

登入發表回應

海綿寶寶

iT邦大神 1 級 ‧ 2019-03-21 23:08:14

Private Function WorksheetExists(ByVal WorksheetName As String) As Boolean
'PURPOSE: Determine if a worksheet name exists in the workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

On Error Resume Next
  WorksheetExists = (ActiveWorkbook.Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function

參考來源