so i found this code on line and it seems to work
i think it is doing what you all said which is map the share point folder to a drive. but the drive
its mapping to is an arbitrary drive in this case in the code its letter "A" (i wonder what would happen in macro if
drive letter "A" is already taken)
the code gets all file names plus a lot of other junk and puts it onto the first sheet inthe file. this is not what
i plan on doing just wanted to see if code worked.
again, do you think i need to alter this in some way to map to non-traditional drive number ...not sure what would happen if i gave macro to someone else
and they already had drive A mapped to something else.
HEre is code i found:
Sub DownloadListFromSharepoint()
Dim SharepointAddress As String
Dim LocalAddress As String
Dim objFolder As Object
Dim objNet As Object
Dim objFile As Object
Dim FS As Object
Dim rng As Range
SharepointAddress = https://abc.sharepoint.com/sites/abc-Auto/Time%20Product%20Tracking/
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
objNet.MapNetworkDrive "A:", SharepointAddress
Set objFolder = FS.getfolder("A:")
Set rng = ThisWorkbook.Worksheets(1).Range("a1")
rng.Value = "File Name"
rng.Offset(0, 1).Value = "Folder/File"
rng.Offset(0, 2).Value = "Path"
GetAllFilesFolders rng, objFolder, "" & strSharepointAddress
objNet.RemoveNetworkDrive "A:"
Set objNet = Nothing
Set FS = Nothing
End Sub
Public Sub GetAllFilesFolders(rng As Range, ObjSubFolder As Object, strSharepointAddress As String)
Dim objFolder As Object
Dim objFile As Object
For Each objFile In ObjSubFolder.Files
rng.Offset(1, 0) = objFile.Name
rng.Offset(1, 1) = "File"
rng.Offset(1, 2) = Replace(objFile.Path, "A:\", SharepointAddress)
Set rng = rng.Offset(1, 0)
Next
For Each objFolder In ObjSubFolder.subfolders
rng.Offset(1, 0) = objFolder.Name
rng.Offset(1, 1) = "Folder"
rng.Offset(1, 2) = Replace(objFolder.Path, "A:\", SharepointAddress)
Set rng = rng.Offset(1, 0)
GetAllFilesFolders rng, objFolder, strSharepointAddress
Next
End Sub
Bookmarks