Macro to open all files in SharePoint Library



I need to write a macro to open all the files in a libray on a SharePoint. I found the code below which looks very straightforward but I get the error"User-defined type not defined" for each of the Dim statements. It appearsthat I need to add some references, but can anyone tell me what referencesI need to select to make this code work?

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("\\SP\Path\MorePath\DocumentLibrary\Folder")
For Each f In folder.Files
Cells(RowCtr, 1).Value = f.Name
RowCtr = RowCtr + 1
Next f
End Sub

As an alternative is there a method that does not require adding references? I have also seen suggestions that mapping the sharepoint to a drive letter would be helpful but this is not an optimum solution as many people will eventually use this tool and it is not likely that they can all map the same drive letter.

Any suggestions or tips would be much appreciated.

Win7/Excel 2010

John Keith

Here is the solution that we have developed:

Sub Execute_Files()
Dim objFSO As Object, objFolder As Object, objFile As Object
Dim Path As String

' Define paths to folders that contain files to execute

path = "http://blah/blah/blah/blah/"
Application.DisplayAlerts = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)

' Loop through the Files in the folder and execute the macro in the
file, then close the file

For Each objFile In objFolder.Files
Workbooks.Open Filename:=Path & objFile.Name
Application.Run ("'" & objFile.Name & "'!macro_name")
Workbooks(objFile.Name).Close savechanges:=False
Application.DisplayAlerts = True
End Sub

I need to write a macro to open all the files in a libray on a SharePoint.

John Keith
(e-mail address removed)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question