使用VBA打开Sharepoint Excel文件 [英] Open Sharepoint Excel Files With VBA

问题描述

我在Excel中使用VBA在共享点网站上循环浏览文件并打开所有Excel文件.

I'm using VBA in Excel to loop through files on a sharepoint site and open all Excel files.

我第一次运行它时，代码使Excel崩溃，但是，如果我重新打开它，它将正常工作.

The code crashes Excel the first time I run it, however, if I then reopen it it works fine.

这附近是否存在任何已知问题?

Are there any known issues around this?

谢谢.

这是代码:

Sub Refresh()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

        Dim fso As FileSystemObject
        Dim fldr As Folder
        Dim f As File
        Dim wb As Workbook

        Set fso = New FileSystemObject
        Set fldr = fso.GetFolder(SharePointSite)

        For Each f In fldr.Files

            Set wb = Workbooks.Open(SharePointURL & f.Name)

        Next f

        Set wb = Nothing
        Set fldr = Nothing
        Set fso = Nothing

        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

推荐答案

请尝试使用WebDAV地址访问代码中的库，而不是将文档库映射到驱动器号.这样，如果分发了宏，则没人会依赖于将"Z:"驱动器映射到特定位置

Instead of mapping the document library to a drive letter try using the WebDAV address to access the library in your code. This way if the macro is distributed no one will be dependent upon having the "Z:" drive being mapped to a specific location

将FilePath变量设置为等于这样的字符串(对于HTTPS站点，请使用@SSL):

Set your FilePath variable equal to a string like this (use @SSL for HTTPS sites):

\\sharepoint.site.com@SSL\DavWWWRoot\site1\usersite\Book2\Shared%20Documents

如果您要直接访问文本文件，请按以下步骤进行设置:

If you are going to access the text file directly then set it up like this:

\\sharepoint.site.com@SSL\DavWWWRoot\site1\usersite\Book2\Shared%20Documents \Test_Text1.txt

\\sharepoint.site.com@SSL\DavWWWRoot\site1\usersite\Book2\Shared%20Documents \Test_Text1.txt

看看此博客文章，详细介绍了如何检索WebDAV路径.

Take a look at this blog post for a full explanation on retrieving the WebDAV path.

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