[VBA ] if函數判斷錯誤?

看板Visual_Basic作者BanPeeBan (踢屁屁)時間6年前 (2018/11/27 20:02)推噓1(1推 0噓 4→)

留言5則, 1人參與討論串1/1

各位大大好延續上次的問題 (文章代碼： #1RzhUY6f ) 這次需要"先判斷工作表是否存在" 如果存在，則跳過(即繼續執行迴圈) 如果不存在，則新增該工作表不知道是不是因為使用了 On Error Resume Next 導致全部皆判定為"工作表存在"，以至於無法新增@@ 請問這該怎麼解決? ------------------------------我是程式碼------------------------------ Sub skip_sheet() mydir = ActiveWorkbook.Path abook = ActiveWorkbook.Name asheet = ActiveSheet.Name Set fs = Application.FileSearch With fs .LookIn = mydir .Filename = "*.lod" If .Execute(msoSortByFileName) Then Nfile = .FoundFiles.Count End If MsgBox ("Nfile = " & Nfile) s = "" For i = 1 To Nfile s = s & .FoundFiles(i) & Chr(10) Next i MsgBox (s) sheetname = "" For i = 1 To Nfile Workbooks.OpenText Filename:=.FoundFiles(i), DataType:=xlDelimited, Tab:=True, comma:=True, Space:=True actb = ActiveWorkbook.Name acts = ActiveSheet.Name Workbooks(actb).Sheets(acts).Activate sheetname = Right(.FoundFiles(i), 10) sheetname = Left(sheetname, 6) Range("A1").Select Selection.Copy Workbooks(abook).Activate On Error Resume Next Set ws = Sheets(sheetname) If ws Is Nothing Then MsgBox ("New Sheet! " & .FoundFiles(i)) Sheets.Add.Name = sheetname 'MsgBox ("Sheet " & sheetname & " Added") Workbooks(abook).Sheets(sheetname).Activate Range("A1").Select ActiveSheet.Paste Else MsgBox ("Old Sheet! " & .FoundFiles(i)) Workbooks(abook).Sheets(asheet).Activate Range("A1").Select End If Workbooks(actb).Activate Application.CutCopyMode = False Workbooks(actb).Close Next i End With End Sub ------------------------------我是參考圖片------------------------------ https://imgur.com/QAHflPp