Macro to open all files in SharePoint Library

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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
Next
Application.DisplayAlerts = True
End Sub

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

John Keith
(e-mail address removed)
 
Back
Top