hobbiton73
03-31-2015, 12:21 AM
Hi, I wonder whether someone can help me please.
From the searching I've done, I know that one method of displaying a 'Save As Dialog' is to use this line of code:
Application.Dialogs(xlDialogSaveAs).Show But could somene perhaps tell me please how I may be able to incoporate it into the following code taken from this page: http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/
Public Sub ListFilesInFolder(SourceFolder As Scripting.folder, IncludeSubfolders As Boolean)
On Error Resume Next
For Each FileItem In SourceFolder.Files
' display file properties
Cells(iRow, 2).Formula = iRow - 6
Cells(iRow, 3).Formula = FileItem.Name
Cells(iRow, 4).Select
Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
FileItem.Path, TextToDisplay:="Click Here to Download"
'Cells(iRow, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"
iRow = iRow + 1 ' next row number
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
What I'm trying to do is allow the user to save the file when they click the 'Click Here to Download' link against a file which has been listed, but I'm not sure how to do this.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this.
Many thanks and kind regards
Chris
From the searching I've done, I know that one method of displaying a 'Save As Dialog' is to use this line of code:
Application.Dialogs(xlDialogSaveAs).Show But could somene perhaps tell me please how I may be able to incoporate it into the following code taken from this page: http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/
Public Sub ListFilesInFolder(SourceFolder As Scripting.folder, IncludeSubfolders As Boolean)
On Error Resume Next
For Each FileItem In SourceFolder.Files
' display file properties
Cells(iRow, 2).Formula = iRow - 6
Cells(iRow, 3).Formula = FileItem.Name
Cells(iRow, 4).Select
Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
FileItem.Path, TextToDisplay:="Click Here to Download"
'Cells(iRow, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"
iRow = iRow + 1 ' next row number
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
What I'm trying to do is allow the user to save the file when they click the 'Click Here to Download' link against a file which has been listed, but I'm not sure how to do this.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this.
Many thanks and kind regards
Chris