Usually I use this piece of code to retrieve the content of a folder in VBA. But this doesn't work in the case of a sharepoint. How can I do ?
Dim folder As folder
Dim f As File
Dim fs As New FileSystemObject
Set folder = fs.GetFolder("//sharepoint.address/path/to/folder")
For Each f In folder.Files
'Do something
Next f
EDIT (after a good comment by shahkalpesh) :
I can access to the sharepoint if I enter the address in Windows Explorer. Access to the sharepoint needs an authentification, but it's transparent, because it relies on the Windows login.
This question is related to
sharepoint
excel
vba
Mapping the WebDAV folder is my preferred method of creating an easily accessible, long-term connection to SharePoint. However, you'll find—even when properly mapped—that a file will return a URL when selected (especially via Application.FileDialog
) due to changes in Windows 10 1803.
To circumvent this, you can map the drive using DriveMapper
(or an equivalent) and then combine the resulting Application.FileDialog.SelectedItems
with a URL to UNC converter function:
Public Function SharePointURLtoUNC( _
sURL As String) _
As String
Dim bIsSSL As Boolean
bIsSSL = InStr(1, sURL, "https:") > 0
sURL = Replace(Replace(sURL, "/", "\"), "%20", " ")
sURL = Replace(Replace(sURL, "https:", vbNullString), "http:", vbNullString)
sURL= Replace(sURL, Split(sURL, "\")(2), Split(sURL, "\")(2) & "@SSL\DavWWWRoot")
If Not bIsSSL Then sURL = Replace(sURL, "@SSL\", vbNullString)
SharePointURLtoUNC = sURL
End Function
The only way I've found to work with files on SharePoint while having to server rights is to map the WebDAV folder to a drive letter. Here's an example for the implementation.
Add references to the following ActiveX libraries in VBA:
wshom.ocx
) - for WshNetworkscrrun.dll
) - for FileSystemObjectCreate a new class module, call it DriveMapper
and add the following code:
Option Explicit
Private oMappedDrive As Scripting.Drive
Private oFSO As New Scripting.FileSystemObject
Private oNetwork As New WshNetwork
Private Sub Class_Terminate()
UnmapDrive
End Sub
Public Function MapDrive(NetworkPath As String) As Scripting.Folder
Dim DriveLetter As String, i As Integer
UnmapDrive
For i = Asc("Z") To Asc("A") Step -1
DriveLetter = Chr(i)
If Not oFSO.DriveExists(DriveLetter) Then
oNetwork.MapNetworkDrive DriveLetter & ":", NetworkPath
Set oMappedDrive = oFSO.GetDrive(DriveLetter)
Set MapDrive = oMappedDrive.RootFolder
Exit For
End If
Next i
End Function
Private Sub UnmapDrive()
If Not oMappedDrive Is Nothing Then
If oMappedDrive.IsReady Then
oNetwork.RemoveNetworkDrive oMappedDrive.DriveLetter & ":"
End If
Set oMappedDrive = Nothing
End If
End Sub
Then you can implement it in your code:
Sub test()
Dim dm As New DriveMapper
Dim sharepointFolder As Scripting.Folder
Set sharepointFolder = dm.MapDrive("http://your/sharepoint/path")
Debug.Print sharepointFolder.Path
End Sub
IMHO the coolest way is to go via WebDAV (without Network Folder, as this is often not permitted). This can be accomplished via ActiveX Data Objects as layed out in this excellent article excellent article (code can be used directly in Excel, used the concept recently).
Hope this helps!
http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/
the original link is dead, but at least the textual content is still available on archive.org: http://web.archive.org/web/20091008034423/http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic
Try mapping the sharepoint library to a drive letter in windows. Then select the drive and path in your code.
I spent some time on this very problem - I was trying to verify a file existed before opening it.
Eventually, I came up with a solution using XML and SOAP - use the EnumerateFolder method and pull in an XML response with the folder's contents.
I blogged about it here.
I messed around with this problem for a bit, and found a very simple, 2-line solution, simply replacing the 'http' and all the forward slashes like this:
myFilePath = replace(myFilePath, "/", "\")
myFilePath = replace(myFilePath, "http:", "")
It might not work for everybody, but it worked for me
If you are using a secure site (or wish to cater for both) you may wish to add the following line:
myFilePath = replace(myFilePath, "https:", "")
Drive mapping to sharepoint (also https)
Getting sharepoint contents worked for me via the mapped drive iterating it as a filesystem object; trick is how to set up the mapping: Then copy path (line with http*) (see below)
Use this path in Map drive from explorer or command (i.e. net use N: https:://thepathyoujustcopied
)
Note: https works ok with windows7/8, not with XP.
That may work for you, but I prefer a different approach as drive letters are different on each pc. The trick here is to start from sharepoint (and not from a VBA script accessing sharepoint as a web server).
Set up a data connection to excel sheet
You can either include this query in vba, or maintain the database link in your speadsheet, iterating over the table by VBA. Please note: the image above does not show the actual database connection (command text), which would tell you how to access my sharepoint.
Use the UNC path rather than HTTP. This code works:
Public Sub ListFiles()
Dim folder As folder
Dim f As File
Dim fs As New FileSystemObject
Dim RowCtr As Integer
RowCtr = 1
Set folder = fs.GetFolder("\\SharePointServer\Path\MorePath\DocumentLibrary\Folder")
For Each f In folder.Files
Cells(RowCtr, 1).Value = f.Name
RowCtr = RowCtr + 1
Next f
End Sub
To get the UNC path to use, go into the folder in the document library, drop down the Actions menu and choose Open in Windows Explorer. Copy the path you see there and use that.
In addition to:
myFilePath = replace(myFilePath, "/", "\")
myFilePath = replace(myFilePath, "http:", "")
also replace space:
myFilePath = replace(myFilePath, " ", "%20")
Source: Stackoverflow.com