Sharepoint library: VBA to loop through all files in library

[Sharpefiction]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I spent hours looking through different means of looping through a SharePoint folder within Excel VBA to get details from the files there and then store them within a spreadsheet.<o></o>
What I was aiming to do was gather all of the information from a 'document library' of excel files (where files were names I didn’t know & therefore couldn't specify beforehand), of varying names & varying quantity, but with the same defined names & so on within the spreadsheet. <o></o>
<o> </o>
Finally, the other day I cracked it. I might be posting this in the wrong place, but I wanted to share in case there is someone like me out there who could use.<o></o>
<o> </o>
I need to say that the initial loop through files was copied from a forum response I found a little while ago and have used in various instances since. Unfortunately, I cannot give credit directly, as I didn’t note their username/the site where I got it from. So thank you for your code.<o></o>
<o> </o>
'Sub ListAllFile()<o></o>
'<o></o>
' Dim objFSO As Object<o></o>
' Dim objFolder As Object<o></o>
' Dim ObjFile As Object<o></o>
' Dim WBo As Workbook<o></o>
' Dim pth As String<o></o>
' Dim WBn As Workbook<o></o>
' Dim FinalRow As Long<o></o>
' Set WBo = ThisWorkbook<o></o>
' Dim ObCount As Long<o></o>
' Dim FileNme As String<o></o>
' Dim FnameMstr As String<o></o>
' Dim SnameMstr As String<o></o>
' Dim RforNomMstr As String<o></o>
' Dim NomCatMstr As String<o></o>
' Dim NamNomMstr As String<o></o>
'<o></o>
' Application.ScreenUpdating = False<o></o>
'<o></o>
' Set objFSO = CreateObject("Scripting.FileSystemObject")<o></o>
'<o></o>
' Application.StatusBar = False<o></o>
'<o></o>
' FinRow = Cells(Rows.Count, 2).End(xlUp).Row + 1<o></o>
'<o></o>
' Cells(2, 2).Resize(FinRow, 6).ClearContents<o></o>
'<o></o>
' 'Get the folder object associated with the directory<o></o>
' Set objFolder = objFSO.GetFolder("//projectspace/ColleagueResponses/")<o></o>
** You'll need to specify your path here. By removing the http: from the path, the code liked it & found the folder. It wasn’t working previously ***<o></o>
' Data.Activate<o></o>
'<o></o>
' pth = http://projectspace/colleagueResponses/<o></o>
** You'll need to specify your path here. The reason I’ve done this separately is because the path is not recognised otherwise when trying to specify it with workbook.open & using the value set for objFolder **<o></o>
'<o></o>
' ObCount = objFolder.Files.Count<o></o>
** counts the number of files in the folder<o></o>
'<o></o>
' 'Loop through the Files collection<o></o>
' For Each ObjFile In objFolder.Files<o></o>
' Nm1 = Len("\\projectspace\colleagueResponses\")
<o>** You'll need to specify your path here **</o>
' Nm2 = Len(ObjFile) - Nm1<o></o>
' FileNme = Right(ObjFile, Nm2)<o></o>
** I’ve done this part to find out/set the file name**<o></o>
<o> </o>
' WBo.Activate<o></o>
' FinRow = Cells(Rows.Count, 2).End(xlUp).Row + 1<o></o>
'<o></o>
' Set WBn = Workbooks.Open(pth & FileNme, , , , Password:="Password1")<o></o>
'<o></o>
' Application.ScreenUpdating = False<o></o>
'<o></o>
' FnameMstr = Range("FName")<o></o>
' SnameMstr = Range("SName")<o></o>
' RforNomMstr = Range("RforNom")<o></o>
' NomCatMstr = Range("NomCat")<o></o>
' NamNomMstr = Range("NamNom")<o></o>
' WBo.Activate<o></o>
' Cells(FinRow, 2).Value = FnameMstr<o></o>
' Cells(FinRow, 3).Value = SnameMstr<o></o>
' Cells(FinRow, 4).Value = RforNomMstr<o></o>
' Cells(FinRow, 5).Value = NomCatMstr<o></o>
' Cells(FinRow, 6).Value = NamNomMstr<o></o>
'<o></o>
' WBn.Close savechanges:=False<o></o>
<o> </o>
'<o></o>
' Next ' loops through each file<o></o>
'<o></o>
' '<o></o>
' Range(Cells(2, 2), Cells(FinRow, 6)).Select<o></o>
' Selection.Sort Key1:=Cells(2, 3), Order1:=xlAscending, Header:=xlGuess, _<o></o>
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o></o>
' DataOption1:=xlSortNormal<o></o>
'<o></o>
' Cells(1, 2).Select<o></o>
'<o></o>
' WBo.Save<o></o>
'<o></o>
'Application.StatusBar = False<o></o>
'<o></o>
'Application.ScreenUpdating = True<o></o>
'<o></o>
'End Sub<o></o>
<o> </o>
<o></o>
I hope this is of some use; I just wanted to give something back really.

Some of the coding could probably be made more efficient, but it does work.

 

[MikeDBMan]

I get stuck at the getfolder line as discussed above.

 

[MikeDBMan]

In your code, the line:

Set objFolder = objFSO.GetFolder(PthNoHttp)

returns error 76, path not found.

I strip out the Https: and any spaces I leave as spaces and not as %20. I also turn the / to \. Suggestions?

 

[MikeDBMan]

I found an alternate way to do this. Check this link:
http://stemrc.aihec.org/SharePoint ...Mapping for a SharePoint Document Library.pdf

Basically what this does is allows one to map a SharePoint site to a windows drive letter. Then a list of files can easily be performed using code that one would normally use for windows directories to list files in a folder, etc. It seems to work just fine.

 

[wheelofthelaw]

After mapping my SharePoint directory to a drive letter, I noticed that the name had some interesting changes:

Directory mapped: "https://mycompany.sharepoint.microsoftonline.com/folder1/folder2/Document Library/"
Displayed name in mapped drive: "\\mycompany.sharepoint.microsoftonline.com@SSL\DavWWWRoot\folder1\folder2\Document Library"

When I tried using the mapped name - including the "@SSL\DavWWWRoot\" - in the GetFolder method of the code, it seems to work fine. Well, very slow, but still fine. I'm not sure exactly what the modified string does (though I assume the SSL is for the Secured Socket Layer used in the https connection), but it appears that it is what Windows needs to use the SharePoint Document Library like a Windows folder.

BTW, I'm using Windows 7, Office 2007, SharePoint 2010.

 

[Andrew Sarkar]

Hi

My sharepoint folder address is << https://team.extsp.ford.com/sites/DIESELDIAGNOSTICS/Global Documents/Test Folder (Tool Experiment)>>

I notice couple of spaces there. Your code is not working. It showing error in GetFolder(" ") command. I gave the command like this:
Set objFolder = objFSO.GetFolder("//team.extsp.ford.com/sites/DIESELDIAGNOSTICS/Global Documents/Test Folder (Tool Experiment)/")

I need your help to fix this.

ANDREW