是否可以通过 VBA 检查共享点站点上是否存在文件? [英] Is it possible to check via VBA if file exist on a sharepoint site?

问题描述

我正在尝试编写一个 Excel (2010) 宏，该宏在某些时候必须确认公司共享站点上某个文件 (doc/pdf) 的存在.该文件可通过 Internet Explorer 访问(授予用户所有权限).我有该文件的直接链接.我不需要打开它，只需检查它是否在那里.

I'm trying to write an Excel (2010) macro that at some point would have to confirm existence of a certain file (doc/pdf) on a corporate sharepoint site. The file is accessible through Internet Explorer (all rights are granted to the user). I have a direct link to that file. I don't need to open it, just check if it's there.

如果这是一个本地文件，我会使用 Dir() 来检查文件是否存在.但是，这不适用于 URI.

If this was a local file, I'd use Dir() to check if the file exists. However, this won't work with URIs.

我尝试了一种基于通过 objHttp 获取的方法，但我收到的唯一响应是一个网页，声明我无权查看此页面"[在标签中].

I tried a method based on GET via objHttp but the only response I recieve is a wepage stating that "I am not authorized to view this page" [in tag].

这是否可行?

推荐答案

试试这个:

Function checkFile(URLStr As String) As Boolean
    Dim oHttpRequest As Object
    Set oHttpRequest = New MSXML2.XMLHTTP60
    With oHttpRequest
        .Open "GET", URLStr, False, [Username], [Password]
        .setRequestHeader "Cache-Control", "no-cache"
        .setRequestHeader "Pragma", "no-cache"
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
    End With
    If oHttpRequest.Status = 200 Then
        checkFile = True
    Else
        checkFile = False
    End If
End Function

URLStr 应该类似于http://sharepoint/site/user.xlsx".在 .Open 行中输入您的用户名/密码以将它们传递到站点，这应该适用于任何 URI(例如，我正在针对 .xlsx 文件对其进行测试).我应该指出，在我的内部 SharePoint 网站上，我不需要传递 UN/PW 来运行此功能，因此如果您最终遇到这种情况，只需从 中删除这些参数.打开 调用.此外，所有标题内容可能都不是必需的，但我总是在我的请求中包含它们，所以我将它们留在了.

URLStr should be something like "http://sharepoint/site/user.xlsx". Enter your Username/Password in the .Open line to pass them to the site, and this should work for any URI (I was testing it against .xlsx files for example). I should point out that on my internal SharePoint sites, I don't need to pass the UN/PW in order to run this function, so if that ends up being the case for you, just remove those parameters from the .Open call. Also, all the header stuff probably isn't necessary, but I always have them in my requests so I left them in.

这篇关于是否可以通过 VBA 检查共享点站点上是否存在文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！