Function IsWorkbookOpen(FileName As String) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(FileName)
On Error GoTo 0
If Not wb Is Nothing Then
IsWorkbookOpen = True
Else
IsWorkbookOpen = False
End If
End Function
sub test()
'获取当前目录路径
currentPath = ThisWorkbook.Path & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
' 获取当前目录
Set folder = fso.GetFolder(currentPath)
' 遍历当前目录下的所有文件
For Each file In folder.Files
' 获取文件名和扩展名
FileName = fso.GetFileName(file.Path)
fileExtension = fso.GetExtensionName(file.Path)
' 判断文件扩展名是否为xls或xlsx,且不包含xlsm
If (fileExtension = "xls" Or fileExtension = "xlsx") And fileExtension <> "xlsm" Then
'打开工作簿
Set wb = Workbooks.Open(currentPath & FileName)
' 如果该工作簿已打开,则保存并关闭
If IsWorkbookOpen(FileName) Then
wb.Save
wb.Close
Set wb = Workbooks.Open(currentPath & FileName)
End If
Set ws = wb.Sheets("sheet1")
ws.Activate
'代码区
end if
next
End sub
04-03
11-21
4839
