SharePoint Map

[Luke777]

Hi all

I've been using the following code from a previous project

Sub DownloadListFromSharepoint() 'This sub checks the Sharepoint folder for files and puts the filename in A1 of Sheet3

    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://URLOfDoom"

    Set objNet = CreateObject("WScript.Network")
    Set FS = CreateObject("Scripting.FileSystemObject")
    objNet.MapNetworkDrive "Y:", SharepointAddress
    
    Set objFolder = FS.getfolder("Y:")
    
    Set rng = ThisWorkbook.Worksheets(2).Range("A1")
    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(0, 0) = objFile.Name
        Set rng = rng.Offset(1, 0)
    Next
    
End Sub

It does exactly what the comment says - checks a folder (which only ever has one file in it at a time - but this file has a variable name) and puts the file name in A1 of Sheet3. Later another bit of code checks the value of A1 to complete a string and import the .txt file (the one file in the folder is always .txt afaik).

The code works well in the old project because there were multiple files (with names that changed) in the folder searched and I needed a list of those files on the sheet - with this project I don't - I just need the one. But this bit of code is a little beyond my scope - instead of assigning the file name to a cell, how can I just add it to a new variable?