- 正则表达式处理
- 文件处理
1.正则表达式处理
Set regex = CreateObject("VBScript.RegExp")
endrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row 'Change pattern for different requirement
Application.EnableEvents = False
With regex
.Pattern = "(SC2)-[A-Z0-9]{5}-[A-Z]{4}-\d{6}$" 'Change pattern for different requirement
.Global = True
End With
For i = 2 To endrow
Set matches = regex.Execute(ActiveSheet.Range("A" & i).Value)
Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp))
Dup = WorksheetFunction.CountIf(Target, ActiveSheet.Range("A" & i).Value)
Dept = Mid(ActiveSheet.Range("A" & i).Value, 11, 4)
BusApp = Mid(ActiveSheet.Range("A" & i).Value, 5, 5)
BusAppChk = Mid(ActiveSheet.Range("P" & i).Value, 1, 5)
BRSChk = Mid(ActiveSheet.Range("A" & i).Value, 5, 3)
If matches.Count = 0 Then
ActiveSheet.Range("A" & i).Interior.ColorIndex = 3
2.文件处理
VBA FileSystemObject (FSO) in Excel – Methods and Properties
https://analystcave.com/vba-filesystemobject-fso-in-excel/
VBS基础篇 - 对象(3) - FileSystemObject对象
https://www.cnblogs.com/sirrah/articles/2349034.html
VBA中的FileSystemObject对象(FSO)和文本流
https://www.cnblogs.com/zzstone/p/5530707.html
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Checklist")
If Len(xFolder.Path) > 0 Then
Filename = Dir(xFolder.Path & "\" & Filename, 0)
Debug.Print xFolder.Name
'Get Most Recent File
Do While Filename <> ""
Debug.Print "Read:" & Filename
If FileDateTime(xFolder.Path & "\" & Filename) > MostRecentDate Then
MostRecentFile = Filename
MostRecentDate = FileDateTime(xFolder.Path & "\" & Filename)
End If
Filename = Dir
Loop
Debug.Print "Selected:" & MostRecentFile
SelectedFile = MostRecentFile
On Error GoTo ErrHandler