excel - 添加 MMYYYY 以将名称保存在变体上

Question

我有一个将工作表拆分为新工作簿并按 B 列中的名称保存它们的代码。我想要做的是在保存每个文件时将“ABC MM_YYYY ”添加到文件名的末尾。

我知道在设置 fileNm 时我应该插入与此类似的行：

Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm_yyyy")

我试过了

fileNm = Dir(loopFolder & "*ABC " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm_yyyy") & ".xlsx")

fileNm = Dir(loopFolder & "ABC " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm_yyyy") & "*.xlsx")

没有运气。如果有人可以指导我解决这个问题，那就太棒了。

Dim loopFolder As String
Dim fileNm As Variant
Dim myFiles As New Collection

loopFolder = "J:\Documents\Hookset\"
fileNm = Dir(loopFolder & "*.xlsx")

Do While fileNm <> ""
    myFiles.Add fileNm
    fileNm = Dir
Loop

*edit - 我正在查看代码的错误部分，日期插入应该在此处，而不是在上面

''' Dim FileExtStr As String Dim FileFormatNum As Long Dim xWs As Worksheet Dim xWb As Workbook Dim FolderName As String Application.ScreenUpdating = False

Set xWb = Application.ActiveWorkbook
FolderName = xWb.Path & "\Hookset\" 
For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        Select Case xWb.FileFormat
            Case 51:
                FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If Application.ActiveWorkbook.HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56:
                FileExtStr = ".xls": FileFormatNum = 56
            Case Else:
                FileExtStr = ".xlsb": FileFormatNum = 50
        End Select
    End If

    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & " ABC " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm_yyyy") & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
Next

'''

我从 xFile 中丢失了，现在运行良好。

标签： excelvba