excel - 在 VBA 代码中的文件夹路径中使用当前月份

Question

大家好，任何人都可以帮助解决这个问题，所以基本上问题是当我运行此代码时，我收到一条错误消息，内容为“编译错误:未找到命名参数”。非常感谢您提前)

Sub SavetoCurrentMonth()
    
    Application.DisplayAlerts = False
     
     ' Check for month folder and create if needed
If Len(Dir("C:\Users\OsmonBek\Documents\macros test\" & Format(Month(Date), "P00-") & Format(Date, "mmmm"), vbDirectory)) = 0 Then
        MkDir "C:\Users\OsmonBek\Documents\macros test\" & Format(Month(Date), "P00-") & Format(Date, "mmmm")
    End If
     
     ' Save File
    ActiveWorkbook.ExportAsFixedFormat Filename:= _
    "C:\Users\OsmonBek\Documents\macros test\" & MonthName(Month(Date), False) & "\" & Format(Date, "mm.dd.yy") & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
     
     
    Application.DisplayAlerts = True
     
     ' Popup Message
    MsgBox "File Saved As:" & vbNewLine & "C:\Users\OsmonBek\Documents\macros test\" & MonthName(Month(Date), False) & "\" & Format(Date, "mm.dd.yy") & ".xlsx"

'
End Sub

Answer 1

错误告诉你 ExportAsFixedFormat 没有名为 FileFormat 的参数.
我会使用类似这个函数的东西来返回当前月份的文件夹名称(如果文件夹不存在，则创建该文件夹)，如 c:\P2022_04\ .

Function getMonthFolderPath() As String
'creates/returns folder name for current month, like: c:\P2022_04\
  Const basePath = "c:\", prefix = "P"
  Dim path As String
  path = basePath & prefix & Format(Date, "yyyy_mm")
  If Dir(path, vbDirectory) = "" Then MkDir path
  getMonthFolderPath = path & "\"
End Function

我添加了一年，因为(我假设)你不是所有的四月都在同一个文件夹中。

这是一个变体，它将(如果需要)创建一个带有一个月子文件夹的年文件夹，并返回该路径:

Function getMonthFolderPath() As String
  Dim path As String
  path = "c:\"
  path = path & Year(Date)
  If Dir(path, vbDirectory) = "" Then MkDir path
  path = path & "\" & Format(Date, "mm")
  If Dir(path, vbDirectory) = "" Then MkDir path
  getMonthFolderPath = path & "\"
End Function

任何一种变体都可用于保存当前文件，如下所示:

Sub saveDemo()
'save this workbook like:  C:\2022\04\2022-04-08.xlsm
  Dim fName As String
  fName = getMonthFolderPath & Format(Date, "yyyy-mm-dd") & ".xlsm"
  Application.DisplayAlerts = False 'ignore overwrite warning
  ThisWorkbook.SaveAs fName, xlOpenXMLWorkbookMacroEnabled
  Application.DisplayAlerts = True
End Sub