make an open link where macro asks everytime you click the button for the location & then remainig macro works as programmed

[program_manager_ABG123]

make an open link where macro asks everytime you click the button for the location & then remainig macro works as programmed

i am using your another VBA program very helpfull but i am stuck where i need select path either form sharepoint or one drive or local drive.
i want to let macro ask everytime whenever i want to run this macro location or path to elect to run the macro.

please help
below is the VBA code using shared by you earlier in another chat

"

Option Explicit

Public Sub Main_List_Folders_and_Files()

    With ActiveSheet
        .Cells.Clear
        List_Folders_and_Files "[COLOR=rgb(247, 218, 100)]D:\Prg[/COLOR]", .Range("A1")
    End With

End Sub


Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long

    Dim FSO As Object
    Dim FSfolder As Object, FSsubfolder As Object, FSfile As Object
    Dim folders As Collection, levels As Collection
    Dim subfoldersColl As Collection
    Dim n As Long, c As Long, i As Long
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folders = New Collection
    Set levels = New Collection
   
    'Add start folder to stack
   
    folders.Add FSO.GetFolder(folderPath)
    levels.Add 0
      
    n = 0

    Do While folders.Count > 0
   
        'Remove next folder from top of stack
       
        Set FSfolder = folders(folders.Count): folders.Remove folders.Count
        c = levels(levels.Count): levels.Remove levels.Count
       
        'Output this folder and its files
       
        'destCell.Offset(n, c).Value = "'" & FSfolder.Name  'OLD
        destCell.Worksheet.Hyperlinks.Add Anchor:=destCell.Offset(n, c), Address:=FSfolder.Path, TextToDisplay:=FSfolder.Name
       
              
        n = n + 1
        c = c + 1
        For Each FSfile In FSfolder.Files
            'destCell.Offset(n, c).Value = "'" & FSfile.Name 'OLD
            destCell.Worksheet.Hyperlinks.Add Anchor:=destCell.Offset(n, c), Address:=FSfile.Path, TextToDisplay:=FSfile.Name
 
            n = n + 1
        Next
              
        'Get collection of subfolders in this folder
       
        Set subfoldersColl = New Collection
        For Each FSsubfolder In FSfolder.SubFolders
            subfoldersColl.Add FSsubfolder
        Next
       
        'Loop through collection in reverse order and put each subfolder on top of stack.  As a result, the subfolders are processed and
        'output in the correct ascending ASCII order
       
        For i = subfoldersColl.Count To 1 Step -1
            If folders.Count = 0 Then
                folders.Add subfoldersColl(i)
                levels.Add c
            Else
                folders.Add subfoldersColl(i), , , folders.Count
                levels.Add c, , , levels.Count
            End If
        Next
        Set subfoldersColl = Nothing
               
    Loop
   
    List_Folders_and_Files = n

End Function

"

 

[program_manager_ABG123]

in the above same marco is it possible to get complete data from sharepoint or onedrive or local drive.. not just folders & files but all data written for the sharepoint with respect to each folder like approved, Responsible.... showing below what i like to convert to excel using this macro... as example

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

 

[program_manager_ABG123]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

hi Peter,
thanks for quick reply.
can you please help to add some lines in this code to be able to select sharepoint in macro instead of location D:\Prog as example

 

[Peter_SSs]

Sorry, I don't use sharepoint so cannot help with that. Hopefully somebody else will chime in.