Open Sharepoint Excel Files With VBA

Question

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

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?

Thanks.

Edit: Here is the code:

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

1. Does it crash with an error? If so - what kind of error? 2. Could you post your code please? Have you put any defence coding? — Juliusz
– Juliusz, Commented Aug 31, 2012 at 15:30
Thanks for the reply. No error comes up, Excel just crashes. I've put the code in the original post. Cheers. — user1423997
– user1423997, Commented Aug 31, 2012 at 15:36
oh, could you let us know versions of Excel and SharePoint? I believe there were some issues with compability between Excel 2003 and latest version of SharePoint. — Juliusz
– Juliusz, Commented Aug 31, 2012 at 15:45
Have you tried mapping sharepoint to a drive letter and using that reference instead of the URL? — Alistair Weir
– Alistair Weir, Commented Sep 6, 2012 at 14:20

Shrout1 · Accepted Answer · 2013-09-09 14:01:01Z

2

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

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

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

answered Sep 9, 2013 at 14:01

Shrout1

2,6374 gold badges47 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Open Sharepoint Excel Files With VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related