Get the content of a sharepoint folder with Excel VBA

Question

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

User · Answer

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   in sharepoint  browse to the view you want to monitor export view to excel  in 2010  library tools  libarry   export to Excel   when viewing this excel  you ll find a datasource set up  tab  data  connections  properties  definition      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

User · Answer

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

User · Answer

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    Windows Script Host Object Model  wshom ocx  - for WshNetwork Microsoft Scripting Runtime  scrrun dll  - for FileSystemObject   Create 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  amp       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  amp          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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   quot https  quot    gt  0   sURL   Replace Replace sURL   quot   quot    quot   quot     quot  20 quot    quot   quot     sURL   Replace Replace sURL   quot https  quot   vbNullString    quot http  quot   vbNullString       sURL  Replace sURL  Split sURL   quot   quot   2   Split sURL   quot   quot   2   amp   quot  SSL DavWWWRoot quot     If Not bIsSSL Then sURL   Replace sURL   quot  SSL  quot   vbNullString     SharePointURLtoUNC   sURL End Function

User · Answer

Try mapping the sharepoint library to a drive letter in windows   Then select the drive and path in your code

User · Answer

In addition to   myFilePath   replace myFilePath            myFilePath   replace myFilePath   http          also replace space   myFilePath   replace myFilePath         20

[sharepoint] Get the content of a sharepoint folder with Excel VBA

Examples related to sharepoint

Examples related to excel

Examples related to vba