Sharepoint Network Map Code Conversion

[Luke777]

Hi all!

About a year ago I used the following code on a project...

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 = "sharepointURL..."



Set objNet = CreateObject("WScript.Network")

Set FS = CreateObject("Scripting.FileSystemObject")

objNet.MapNetworkDrive "Y:", SharepointAddress



Set objFolder = FS.getfolder("Y:")



Set Rng = ThisWorkbook.Worksheets("chonk").Range("M7")

GetAllFilesFolders Rng, objFolder, "" & strSharepointAddress

objNet.RemoveNetworkDrive "Y:"

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

Set Rng = Rng.Offset(1, 0)

Next



End Sub

Which worked very nicely for what I wanted it to do at the time.

As you can see, it dumps the filename in M7 (actually M8 for some reason - there's only one file in the folder anyway so it really doesn't matter too much).

I've made a few attempts to convert the code so instead of the filename being dumped on the sheet, it creates a new variable instead which I can use elsewhere in the code - for example, opening the file in the folder. My attempts didn't work though unfortunately. Sad noise.

To be clear, I'm using this method because the file in question changes name a lot, which is obviously annoying when it comes to VBA

Thanks for any help!