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.

 

[Biz]

Mate i tried but it does not work.
Can tell me what range defintion ie what columns and rows?

FnameMstr = Range("FName")
SnameMstr = Range("SName")
RforNomMstr = Range("RforNom")
NomCatMstr = Range("NomCat")
NamNomMstr = Range("NamNom")


Biz

 

[Sharpefiction]

[FONT=&quot]Sorry, I think I’ve confused things by leaving my coding in. I’ll remove everything but that needed to make the loop work, you just need to add in your bits specific for your spreadsheets[/FONT]

[FONT=&quot]Sub ListAllFile()[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Dim objFSO As Object[/FONT]
[FONT=&quot]' Dim objFolder As Object[/FONT]
[FONT=&quot]' Dim ObjFile As Object[/FONT]
[FONT=&quot]' Dim pth As String[/FONT]
[FONT=&quot]' Dim WBn As Workbook[/FONT]
[FONT=&quot]' Dim ObCount As Long[/FONT]
[FONT=&quot]' Dim FileNme As String[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Application.ScreenUpdating = False[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Set objFSO = CreateObject("Scripting.FileSystemObject")[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' 'Get the folder object associated with the directory[/FONT]
[FONT=&quot]' Set objFolder = objFSO.GetFolder("//projectspace/ColleagueResponses/")[/FONT]
[FONT=&quot]** 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 ***[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' pth = http://projectspace/colleagueResponses/[/FONT]
[FONT=&quot]** 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 **[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' ObCount = objFolder.Files.Count[/FONT]
[FONT=&quot]** counts the number of files in the folder[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' 'Loop through the Files collection[/FONT]
[FONT=&quot]' For Each ObjFile In objFolder.Files[/FONT]
[FONT=&quot]' Nm1 = Len("\\projectspace\colleagueResponses\")[/FONT]
[FONT=&quot]** You'll need to specify your path here **[/FONT]
[FONT=&quot]' Nm2 = Len(ObjFile) - Nm1[/FONT]
[FONT=&quot]' FileNme = Right(ObjFile, Nm2)[/FONT]
[FONT=&quot]** I’ve done this part to find out/set the file name**[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Set WBn = Workbooks.Open(pth & FileNme, , , , Password:="Password1")[/FONT]
[FONT=&quot]** opens the first file in the library – if there is no password, the remove everything from - , , , , Password:="Password1" – leaving the close bracket ‘)’[/FONT]

[FONT=&quot]' Application.ScreenUpdating = False[/FONT]
[FONT=&quot]** optional – you can leave the screen updating on[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]<< Your coding here>>[/FONT]
[FONT=&quot]** The file is now open. Enter whatever code is specific to your spreadsheets.[/FONT]
[FONT=&quot]'[/FONT]
[FONT=&quot]' Next[/FONT]
[FONT=&quot]** goes to next file within your sharepoint folder[/FONT]

[FONT=&quot]'End Sub[/FONT]

[FONT=&quot]Hope this helps[/FONT]

 

[Biz]

The macros belong get list of files in Sharepoint under location

Sub Create()
    EnableReference
    CreateList
End Sub
Private Sub EnableReference()
 
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile "C:\Windows\system32\scrrun.dll"
On Error GoTo 0
End Sub
Private Sub CreateList()
    Dim folder As folder
    Dim f As File
    Dim fs As New FileSystemObject
    Dim RowCtr As Integer
    RowCtr = 1
    Set folder = fs.GetFolder("[COLOR=#000080]\\projectspace\colleagueResponses\[/COLOR]") '<=Variable Location
    For Each f In folder.Files
       Cells(RowCtr, 1).Value = f.Name
       RowCtr = RowCtr + 1
    Next f
End Sub

May be helpful for somebody.

Biz

 

[Sharpefiction]

Thanks Biz. My coding was aimed at getting information from within the spreadsheets, whereas your coding creates a list of the names within the spreadsheet you're running the macro from.

I suppose it will depend on what you're after from the loop & the information it can provide. I wrote mine that way as I was having two issues - one was creating a loop for a sharepoint library directory; two was opening the file because of the variance of the file name. The code I posted does that job perfectly & I posted as I couldn't find a previously posted solution to my problem.

Thanks for your coding though. As I said at the bottom of my original post, there is probably more efficient coding that can be used & your loop looks like it could be! (I'll have to test first before I commit ) Hopefully one or both can be useful.

 

[Biz]

Hi Sharpefiction,

It is cool as we all are learning through forums everyday.

Biz

 

[jhol4]

Hi there

I know this is an old thread, however wondering if someone could help me. I have used (and tweaked) the code provided by Sharpefiction, however it bugs out at the following line:

Set objFolder = objFSO.GetFolder("//projectspace/ColleagueResponses/")

It says it can't find the path, however I know the path exists (I've tried removing http: but still doesnt work) *note i have used my own path not the above.

I think the issue is due to username and password restrictions. Is there anyway to adapt the code to allow the username and password to be entered?

I have also tried adding code before this to open up the directory in Internet Explorer and enter username / password. This works and gets me into the sharepoint directory, however I don't know how to then run the code to list out the files within the folder.

Thanks in advance.

 

[Sharpefiction]

It's an interesting one.. I've used my code on lots of different occasions & it's always worked. You need to have access to the SharePoint library - read access might be acceptable, though worth getting at least the next one up - & then make sure the file path is correct.

Have you tried stepping through the code, bit by bit to see which part of the code is causing the issue? - sorry if you've already done that, but it's always my first step.

Can I also check that you're using the forward slashes & back slashes on the right bits? That caught me when first trying the code.

If you can step through & then let me know which part comes up with invalid path/cannot find path etc, I'll try to help more.

 

[MikeDBMan]

I can't get this to work. Has anyone actually been able to get a listing of SharePoint files in a folder?

 

[Sharpefiction]

I can't get this to work. Has anyone actually been able to get a listing of SharePoint files in a folder?

Hi Mike,

Which code are you trying? The code I posted works for going through each file within a SharePoint library/folder & getting information from within those files to then populate the spreadsheet the macro is being run from; it doesn't provide a list of files within the library/folder. The code posted by Biz on the other hand, seems like it's aimed at providing a list of the file names within the library/folder.