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

Joined
Feb 18, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

"
VBA Code:
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
"
 
Last edited by a moderator:
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
 

Attachments

  • Capture macro help.JPG
    Capture macro help.JPG
    70.9 KB · Views: 5
Upvote 0
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. 😊
 
Upvote 0
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
 
Upvote 0
Sorry, I don't use sharepoint so cannot help with that. Hopefully somebody else will chime in.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top