This is a discussion on VBA Save As Dialog within the Excel Questions forums, part of the Question Forums category; Hi, I wonder whether someone could help me please. From an article here: Welcome to LearnExcelMacro.com How to get list ...
Hi, I wonder whether someone could help me please.
From an article here: Welcome to LearnExcelMacro.com How to get list of All files in a Folder and Sub-folders , I'm using the script below to apply a link to a list of file names which the user can view by clicking on said link.
The problem I have is that to save the file, the user has to view it, then save it.Code: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 Save" 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
Could someone perhaps provide some guidance on how I may be able to change this so that instead of opening the file, the link takes the user straight to the 'Save As Dialog' .
Many thanks and kind regards