Hi, I wonder whether someone could help me please.
I've put together this code with help from an online tutorial which fetches a list files from a given folder and creates a list of these on a Excel sheet with a link against each which allows the user to open a file.
Public Sub ListFilesInFolder(SourceFolder As Scripting.folder, IncludeSubfolders As Boolean) Dim LastRow As Long On Error Resume Next For Each FileItem In SourceFolder.Files ' display file properties Cells(iRow, 3).Formula = iRow - 12 Cells(iRow, 4).Formula = FileItem.Name Cells(iRow, 5).Select Selection.Hyperlinks.Add Anchor:=Selection, Address:= _ FileItem.Path, TextToDisplay:="Click Here to Open" iRow = iRow + 1 ' next row number With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row End With For Each Cell In Range("C13:E" & LastRow) ''change range accordingly If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5 Cell.Interior.Color = RGB(232, 232, 232) ''color to preference Else Cell.Interior.Color = RGB(141, 180, 226) 'color to preference or remove End If Next Cell Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub
The problem I'm having is that I need to change this so that rather than opening the file, the link allows the user to save the file to a folder of their choice.
But I've been working on this for well over a week now without any success.
I've tried using the command 'Application.Dialogs(xlDialogSaveAs).Show' which I'd hoped would worked but this doesn't. I've attached a sample file which shows a list of files as the user would see this,
- To use your own data you will need to change the file path in the 'btnFetchFiles' sub routine.
- Then select the checkbox 'Select All Types of Files' and click the button next to it.
I just wondered whether someone could possibly look at this please and let me know where I've gone wrong.
Many thanks and kind regards