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 SubThe 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
Chris