excel - 如何检查工作表是否存在?
问题描述
我想在特定路径的子文件夹中搜索具有特定名称的 Excel 书籍。找到后,检查文档是否有特定的工作表。如果没有,请插入另一个文件中的工作表并关闭文档。
我需要遍历特定路径中的每个文件夹(总共大约 300 个文件)。
Public strDestinationPath As String
Public strSearch As Variant
Sub SearchFolders()
Range("B:M").ClearContents
Range("B1").Value = "Name"
Range("C1").Value = "Path"
Range("D1").Value = "Size (KB)"
Range("E1").Value = "DateLastModified"
Range("F1").Value = "Attributes"
Range("G1").Value = "DateCreated"
Range("H1").Value = "DateLastAccessed"
Range("I1").Value = "Drive"
Range("J1").Value = "ParentFolder"
Range("K1").Value = "ShortName"
Range("L1").Value = "ShortPath"
Range("M1").Value = "Type"
Range("B1").Select
Dim strPath As String
strPath = UserGetFolder & "\"
strSearch = InputBox("Enter Search Criteria (Case Sensitive)")
Dim OBJ As Object
Dim Folder As Object
Dim File As Object
Set OBJ = CreateObject("Scripting.FileSystemObject")
Set Folder = OBJ.GetFolder(strPath)
Call ListFiles(Folder)
Dim SubFolder As Object
For Each SubFolder In Folder.SubFolders
Call ListFiles(SubFolder)
Call GetSubFolders(SubFolder)
Next SubFolder
If Range("B2").Value = "" Then
MsgBox "No Files Found", vbInformation
Else
End If
Range("B1").Select
End Sub
Private Sub ListFiles(ByRef Folder As Object)
For Each File In Folder.Files
If InStr(File.Name, strSearch) <> 0 Then
ActiveCell.Offset(1, 0).Select
ActiveCell = File.Name
ActiveCell.Offset(0, 1) = File.Path
ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
ActiveCell.Offset(0, 3) = File.DateLastModified
ActiveCell.Offset(0, 4) = File.Attributes
ActiveCell.Offset(0, 5) = File.DateCreated
ActiveCell.Offset(0, 6) = File.DateLastAccessed
ActiveCell.Offset(0, 7) = File.Drive
ActiveCell.Offset(0, 8) = File.ParentFolder
ActiveCell.Offset(0, 9) = File.ShortName
ActiveCell.Offset(0, 10) = File.ShortPath
ActiveCell.Offset(0, 11) = File.Type
Else
End If
Next File
End Sub
Private Sub GetSubFolders(ByRef SubFolder As Object)
Dim FolderItem As Object
For Each FolderItem In SubFolder.SubFolders
Call ListFiles(FolderItem)
Call GetSubFolders(FolderItem)
If File = Survey_Additional_Info Then
Call WorksheetExists
Call CopySheetToClosedWB
Else
'do nothing
End If
Next FolderItem
End Sub
Function UserGetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
UserGetFolder = sItem
Set fldr = Nothing
End Function
Function WorksheetExists(ByVal WorksheetName As String) As Boolean
'Code to find sheet in a file - added by me not part of original
Dim Sht As Worksheet
For Each Sht In closedBook.Worksheets
If Application.Proper(Time_Slots) = Application.Proper(Time_Slots) Then
WorksheetExists = True
Exit Function
Else: Call CopySheetToClosedWB
End If
Next Sht
WorksheetExists = False
End Function
Sub CopySheetToClosedWB() 'Copy Worksheet to a Closed Workbook
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("S:\Accordant\SUS\NewTimeSlotTab.xlsx")
Sheets("Time_Slots").Copy Before:=closedBook.Sheets(Alternative_Locations)
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
我的代码最多
Call WorksheetExists
解决方案
该函数WorksheetExists()
不知道变量closedBook是什么。该变量未传递,也不是Publlic。
编辑#1:
通常,在为变量赋值之前,您不能使用变量。
例如在该行之前:
For Each Sht In closedBook.Worksheets
应该有一个Dim语句closedBook
和一个Set语句。
Dim和Set应该放在包含For的函数中。
推荐阅读
- javascript - 如何用硒找出 html 变量的值
- push-notification - OneSignal 定制订阅铃
- regex - 为什么 react-router 正确处理正则表达式“路径”,但同时抛出错误?
- express - 未找到模块(nodemon)(未找到 package.json) DOCKER ISSUE
- javascript - 通过使用纯 javascript 在外部单击关闭
- c# - 打印所有学生的成绩分布图,使用星号确定每次出现成绩的时间
- php - 如何在元素之后添加代码?
- batch-file - 批处理/cmd - 子程序不通过参数“返回”数组
- java - 如何在改造 2 中正确处理重定向作为响应
- sql - 获得 2 列的总和,每列具有独特的条件