使用 VBA 从 Sharepoint 中的文件夹中获取文件名 [英] Getting filenames from a folder in Sharepoint with VBA

问题描述

我对此进行了大量研究并找到了许多帮助站点，但仍然无法理解为什么有时这不起作用.

I've done alot of research on this and found a number of help sites but still can't understand why this sometimes doesn't work.

我正在尝试访问一个 sharepoint 站点(对我没有限制)并提取该站点内文件夹中的所有文件.

I'm trying to access a sharepoint site (to which there are no restrictions for me) and extract all the files in a folder within that site.

有时我的 Path 有效并且它确实有效，有时则无效.我有一种感觉，如果我之前在浏览器上进入过 sharepoint 站点，但无法确认(因为我现在又试了一次，但它不起作用 ARGGH).但下面的相同代码在过去也有效.

Sometimes my Path works and it does it, other times it does not. I have a feeling it works if I've gone into the sharepoint site on my browser before but can't confirm that (because I just tried it again now and it doesnt work ARGGH). But the same code below has worked in the past.

它在下面的文件系统对象功能上失败

It's failing on the File System Object function below

Public Function GetFullFileName(strfilepath As String, _
strFileNamePartial As String) As String
Dim objFS As Variant
Dim objFolder As Variant
Dim objFile As Variant
Dim intLengthOfPartialName As Integer
Dim strfilenamefull As String
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strfilepath)
'work out how long the partial file name is
intLengthOfPartialName = Len(strFileNamePartial)

For Each objFile In objFolder.Files
    'Test to see if the file matches the partial file name
    If Left(LCase(Replace(objFile.Name, " ", "")), intLengthOfPartialName) = LCase(strFileNamePartial) Then
    'get the full file name
        strfilenamefull = objFile.Name
        Exit For
    Else
    End If
Next objFile

'Return the full file name as the function's value
GetFullFileName = strfilenamefull
End Function

当它到达 GetFolder(strfilepath) 代码时，我收到运行时错误 '76':找不到路径"

I get a "Run-time error '76': Path not found" when it gets to the GetFolder(strfilepath) code

strfilepath 只是一个常规的共享点站点名称(例如 \teams.uk\gm\FX\SharedDocuments\London\11)November 2013\20 November\Reports)

The strfilepath is just a regular sharepoint site name (e.g. like \teams.uk\gm\FX\SharedDocuments\London\11) November 2013\20 November\Reports)

如前所述，我尝试了文件路径的不同变体，包括 DavWWW，但似乎没有任何效果，我不知道还能尝试什么.

As mentioned I've tried different variations of the file path including DavWWW but nothing seems to work and I dont know what else to try.

有什么建议吗?

谢谢

赖妍

推荐答案

必须启动 webclient 服务才能使用 FileSystemObject 访问 SharePoint.

The webclient service must be started to Access SharePoint using the FileSystemObject.

这个服务在你第一次登录的时候经常没有启动.但是，当您通过普通用户界面访问 SharePoint 时，它就会启动.我认为这就是您的代码不一致的原因.

This service is often not started when you first log on. However as you access SharePoint via the normal user interface it gets started. I think this is why your code works inconsistently.

如果您在本地机器上拥有管理员权限，您可以手动(或使用代码)启动该服务.但是，如果您没有权限，那么您可以尝试一个技巧——虽然笨拙但有效.

If you have admin rights on the local machine you can start the service manually (or using code). However if you don't have rights then you can try a trick - it's clumsy but works.

使用 VBA 代码在资源管理器视图中打开一个已知的 SharePoint 文件夹，然后将其关闭.这将触发 webclient 启动.

Using VBA code open a known SharePoint folder in explorer view then close it. This will trigger webclient to start.

这篇关于使用 VBA 从 Sharepoint 中的文件夹中获取文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！