Can anyone tell me how to access Excel Documents on a SharePoint site using
VBA.


Thanks

EM

I am trying to loop through my personal folder with the following code below.
The URL is what appears in the explorer bar when I go to my Personal folder
on the SharedPoint Site. However it fails on the line of code:

Set oFolder = oFSO.GetFolder(strPath)

What am I doing wrong?

Sub ListAllFilesInFolder()
Dim strPath As String
Dim oFSO As Object
Dim oFile As Object
Dim oFolder As Object
Dim N As Long
N = 1

strPath =
"http://mysite.cnpl.Aco.com/personal/username/Personal%20Documents/Forms/AllItems.aspx" '<<< CHANGE

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strPath)
Cells(N, 1).Value = oFolder.Path
N = N + 1
For Each oFile In oFolder.Files
Cells(N, 2).Value = oFile.Name
N = N + 1
Next 'oFile

Set oFSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing
End Sub

Thanks

EM

"ExcelMonkey" wrote:

Can anyone tell me how to access Excel Documents on a SharePoint site using
VBA.


Thanks

EM

This should work:
strPath =
"\\mysite.cnpl.Aco.com\personal\username\Personal% 20Documents"