excel - 创建文件夹并将文件保存到共享点站点的宏

Question

我有一个公司共享点站点，我和同事需要在该站点上加载年度文件夹、一个月子文件夹和一周结束子文件夹中的每日工作表。我正在寻找一种方法来根据需要自动创建文件夹，而无需连续创建它们。谢谢

我已经尝试如下，但它出现了运行时错误 52。

Private Sub CommandButton5_Click()

Dim sFFname As String
Dim sFname As String
Dim sName As String
Dim sFolder As String
Dim sSub As String


sFolder = Range("B69").Value
sSub = Range("B70").Value
sName = Range("B71").Value
sFname = Range("B72").Value
sFFname = Range("B73").Value
    
    
'Define Folder Path for year folder
 sFolderPath = sFolder
      
'Check Specified Folder exists or not
 If Dir(sFolderPath, vbDirectory) = "" Then

'If folder is not available
MkDir (sFolderPath)

'Display Message
MsgBox "Year folder created successfully!", vbInformation, "VBAF1"


Else

    'If folder is available
    MsgBox "Year Folder already exists!", vbInformation, "VBAF1"
    
End If
     
   'Variable declaration
    
'Define Folder Path for Month folder
sFolderPath = sSub
    
'Check Specified Folder exists or not
If Dir(sFolderPath, vbDirectory) = "" Then

'If folder is not available
MkDir (sFolderPath)

'Display Message
MsgBox "Month folder created successfully!", vbInformation, "VBAF1"


Else

    'If folder is available
    MsgBox "Month Folder already exists!", vbInformation, "VBAF1"
    
End If
     
'Variable declaration
    
'Define Folder Path for Week Ending Folder
sFolderPath = sName
    
'Check Specified Folder exists or not
If Dir(sFolderPath, vbDirectory) = "" Then

'If folder is not available
MkDir (sFolderPath)

'Display Message
MsgBox "Week Ending folder created successfully!", vbInformation, "VBAF1"
  
Else

    'If folder is available
    MsgBox "Week Ending folder already exists!", vbInformation, "VBAF1"
    
End If


'Set up file to be saved

Dim filename As String ''''filename is the sharepoint site and file name
DirFile = sFFname
If Len(Dir(sFFname)) = 0 Then

'If File exists
MsgBox "File already exists! Open exizting file to edit", vbInformation, "VBAF1"

Else

'Insert commands to Save File below

Folder = Range("B72").Value
Name = Range("H73").Value

 ActiveWorkbook.SaveAs filename:= _
    "Folder & " / " & Name & """
     FileFormat = xlOpenXMLWorkbookMacroEnabled

MsgBox "File saved", vbInformation, "VBAF1"
End If
      
'Display Message
MsgBox "Yipeeee!", vbInformation, "VBAF1"

End Sub

单元格引用的格式为： \mycompany.sharepoint.com\sites\mysite\folder\subfolder
开头有 2 个斜杠 - 由于某种原因未在问题中显示

我尝试了文件夹名称和 %20 中有空格的空格，以及将 \ 反转为 / 以及两者的组合。

错误从第一个 If Dir(sFolderPath, vbDirectory) = "" Then

将鼠标悬停在 sFolderPath 上会显示来自单元格的链接

是格式问题还是代码问题？任何帮助表示赞赏。

标签： excelvba