时间戳

首页 > 解决方案 > 检查对 Sharepoint 文件夹的访问权限/如果该文件夹存在

excel - 检查对 Sharepoint 文件夹的访问权限/如果该文件夹存在

问题描述

将简单的宏保存为 SharePoint 的基本 excel 文件，然后执行许多其他操作。当用户有权访问指定文件夹 FLUX PL 时，该宏可以完美运行。

ActiveWorkbook.SaveAs Filename:="https://xxxxcorp.sharepoint.com/sites/CEEControlling/Shared%20Documents/Reporting/FLUX%20Analysis/FLUX%20PL/FLUX%20analysis%20PL%20" & Date & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

如果用户没有访问权限（所以他看不到文件），它会在原始文件的位置保存一半，所以它被破坏了，我必须转到以前的版本。

我尝试包含代码以检查我是否有权访问/SharePoint 中存在该文件夹，但它给了我

运行时错误“52”：错误的文件名或编号。

mypath = "https://xxxxcorp.sharepoint.com/sites/CEEControlling/Shared%20Documents/Reporting/FLUX%20Analysis/FLUX%20PL"
mypath = Replace(Replace(mypath, "https:", ""), "/", "\")
mypath = Replace(mypath, Split(mypath, "\")(2), Split(mypath, "\")(2) & "@SSL")

If Dir(mypath, vbDirectory) = "" Then
    MsgBox ("Doesnt exist!")
Else:
    MsgBox ("Exists!")
End If

我也想过，On Error Goto [label]但是当它给我错误时，文件已经被重命名（并且原来的文件被破坏了）。如果有任何帮助，我将不胜感激。

标签： excelvbasharepoint

解决方案

如果将来有人需要它，我可以让它工作。我尝试保存它，如果它给我错误我使用On Error GoTo并打开另存为对话框。

    On Error GoTo savior
    ActiveWorkbook.SaveAs Filename:= "https://xxxxcorp.sharepoint.com/sites/CEEControlling/Shared%20Documents/Reporting/FLUX%20Analysis/FLUX%20" & Range("H2").Value & "/FLUX%20analysis%20" & Range("H2").Value & "%20" & Date & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False               
    Application.DisplayAlerts = False
    On Error Goto 0
    GoTo rest
savior:
    MsgBox ("You do NOT have access to the default folder on Teams:" & vbNewLine & "Controlling CEE >> Reporting >> Files >> FLUX Analysis >> FLUX " & Range("H2").Value & vbNewLine & vbNewLine & "Select different location for the new trimmed file!")
    Filename = "FLUX analysis " & Range("H2") & " " & Date
    varResult = Application.GetSaveAsFilename(FileFilter:= _
                "Excel Files (*.xlsx), *.xlsx", Title:="Select File Location", _
                InitialFileName:=Filename)
    If varResult <> False Then
        ActiveWorkbook.SaveAs Filename:=varResult, _
        FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = False
    End If
    On Error Goto 0
    GoTo rest
rest:

推荐阅读