VBA - 检查文件夹/文件存在于 SharePoint 中 [英] VBA - Checking Folder/File exist in SharePoint

问题描述

我想通过单击图像使用 VBA 将本地文件复制到共享点库.现在似乎我无法检查 Folder &SharePoint 上的文件.

I wanted to copy a local file to sharepoint library using VBA by clicking an image. Right now seems like I'm unable to check for Folder & Files on SharePoint.

每次我运行代码(通过单击 Excel 中的图像)时，它都会返回无法在 SharePoint 中找到该文件.并停止返回 MsgBox 对不起，没有这样的文件夹......

As every time I ran the code(by clicking an image in excel), it returns unable to find the file in SharePoint. And stops at returning the MsgBox Sorry there's no such Folder......

我尝试映射驱动器，它工作得很好，但不是一个选项，因为最终用户需要自己映射驱动器.所以现在我希望使用链接连接到 SharePoint.

I tried mapping drive, it works perfectly fine, but not an options because end-user need to map the drive by themselves. So now I'm looking to connecting to SharePoint using the link.

如果我将 SharePointLink 复制到 IE &Chrome 使用 \，它工作正常.但是如果我使用/，IE 无法找到链接.

If I copy the SharePointLink to IE & Chrome using \, it works fine. But if I uses /, IE is unable to find the link.

更新

如果我使用\几次后，IE会在网络中打开文件路径.Chrome 将在 chrome 页面上显示文件路径.为什么会这样???????

If I uses \ after few tries, IE, will open up the file path in NetWork. Chrome will show the file path on chrome page. Why is this happening?????

身份验证使用的是 Windows 身份验证，所以不是问题.

The authentication is using windows authentication, so not an issue.

这是我的代码

Sub imgClicked()

Dim SharePointLib As String
Dim MyPath As String
Dim folderPath As String
Dim objNet As Object
Dim FSO As Object
Dim copyPath As String
Dim copyFilePath As String

folderPath = Application.ThisWorkbook.path
MyPath = Application.ThisWorkbook.FullName

SharePointLib = "//company.com/sites/MS/10%20Mg%20Review/"
' create new folder to store the file
copyPath = folderPath + "\copyPath\" 

If Not FolderExists(copyPath) Then
    FolderCreate (copyPath)
ElseIf Not FolderExists(SharePointLib) Then
    MsgBox "Sorry there's no such folder. Folder Path: " & vbNewLine & vbNewLine & SharePointLib & ""
    Exit Sub
End If

fileName = "hello.xlsm"
'Copy current excel file and save at the new folder created
ThisWorkbook.SaveCopyAs copyPath & fileName
MsgBox "Save Copy As: " + copyPath & filseName & vbNewLine & vbNewLine & "The file will be uploaded to this address: " + SharePointLib & fileName

' Check whether the file exist in the directory
' If exist error message
' else copy the file from copyPath then paste at the SharePoint directory
If Not Dir(SharePointLib & fileName, vbDirectory) = nbNullString Then
    MsgBox "Sorry file already exist!"
Else
    Call FileCopy(copyPath & fileName, SharePointLib & fileName)
    MsgBox "File has being successfuly created in SharePoint!"
End If

Set FSO = CreateObject("scripting.filesystemobject")
If Right(copyPath, 1) = "\" Then
    copyPath = Left(copyPath, Len(copyPath) - 1)
End If
If FSO.FolderExists(copyPath) = False Then
    MsgBox copyPath & " doesn't exist"
    Exit Sub
End If
FSO.DeleteFolder copyPath
MsgBox "Folder has being deleted successfully!"

End Sub

检查文件夹是否存在的功能

Function for checking if folder exists

Function FolderExists(ByVal path As String) As Boolean

FolderExists = False
Dim FSO As New FileSystemObject

If FSO.FolderExists(path) Then FolderExists = True

End Function

创建文件夹的功能

Function FolderCreate(ByVal path As String) As Boolean

FolderCreate = True
Dim FSO As New FileSystemObject

try:
If FSO.FolderExists(path) Then
    Exit Function
Else
    On Error GoTo catch
    FSO.CreateFolder path
    Debug.Print "FolderCreate: " & vbTab & path
    Exit Function
End If

catch:
MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
FolderCreate = False
Exit Function

End Function

感谢任何帮助和建议.如果需要更多信息，请告诉我.提前致谢.

Any help and suggestions are appreciated. Let me know if more info is needed. Thanks in advance.

推荐答案

确保 WebClient 服务正在运行.您可以通过代码启动WebClient服务，也可以将启动类型设置为自动.

Ensure the WebClient service is running. You can start the WebClient service through code, or you could set the startup type to automatic.

随着 WebClient 服务的运行，您的文件夹/文件测试将按预期工作.

With the WebClient service running, your folder/file tests will work as expected.

此外，如果您将共享点 url 映射到驱动器号，Windows 将启动 WebClient 服务.

Additionally, if you map the sharepoint url to a drive letter, Windows will start the WebClient service.

Sub mapPath(str_drive as string, str_path as string)
  If Not Len(str_drive) = 1 Then Exit Sub
  Dim wso As Object
  Set wso = CreateObject("WScript.Network")
  wso.MapNetworkDrive str_drive & ":", str_path, False
End Sub

这篇关于VBA - 检查文件夹/文件存在于 SharePoint 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！