VBA Save File As

Question

I wonder whether someone could help me please.

I'm using the code below to dynamically create a list of files from a given folder.

In column E for each row of the list there is a link 'Click Here to Open' which allows the user to open each file.

But I'm now looking to change this so rather than opening the file, the link will open a 'Save' dialog which allows the user the file to a user selected folder, and I must admit this issue has had me baffled for over a week now.

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

I've tried using the command 'Application.Dialogs(xlDialogSaveAs).Show' in every row of the code but I cannot get this to work because all it does is ask the user to save the file as it creates the list.

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

It is not clear what is meant by saving in this context, but one approach to catching the click is to add a Worksheet event for SelectionChange which watches your desired column with a `If Intersect(Target, Range("E:E")) Is Not Nothing Then...`. If that is satisfied, you can prompt for the save and then show the save dialog if yes. The end result would be that clicking in the cell (i.e. changing selection) prompts the dialogs. These sorts of SelectionChange events can be confusing for normal users though since it overrides normal Excel behavior. — Byron Wall, Apr 10 '15 at 18:44
Hi @Byron Wall, thank you for taking the time to come back to me with this and forgive me for not being clear. I've found a link here [link](http://stackoverflow.com/questions/22662795/vba-or-formula-to-open-hyperlink-from-a-cell-and-save-rename-downloaded-file-f) which will hopefully explain things in a little more detail but I'm not sure how to adapt this so the file being opened and saved is not an Excel workbook. In addition if it helps I can post a file on Dropbox. Many thanks and kind regards. Chris — IRHM, Apr 11 '15 at 13:21
Are you ultimately just trying to download files from a location and give them a name? Something similar to http://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer ? — Byron Wall, Apr 11 '15 at 18:47
Hi, thank you for coming back to me with this I really appreciate it. No I'm not trying to open a file from a website. I have a selection of .dat files which are used for Dragon speech recognition software. These are stored in a shared folder. When a user clicks on a link to these from the Excel index sheet as highlighted above, rather than the 'Open' file dialog popping up on screen, I would like the 'Save' dialog box to pop up allowing the user to save the file to a local drive. Kind Regards. Chris — IRHM, Apr 12 '15 at 15:39

Byron Wall · Answer 1 · 2015-04-12T21:19:52.310

0

Here is the relevant code for copying a file from a given spot to a destination folder selected by the user. I wrapped it in a Worksheet event for FollowHyperlink since it sounds like you are doing this based on a click.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim FSO
    Dim sFile As String
    Dim sDFolder As String

    'path to file to copy, you will want to point this at a cell range
    'this assume a single cell is selected
    sFile = Target.Range.Value

    'destination folder
    Dim fldr As FileDialog
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

    fldr.AllowMultiSelect = False
    fldr.Show

    'add the end slash for the copy operation
    sDFolder = fldr.SelectedItems(1) & "\"

    'FSO object to copy the file... True below overwrites if needed
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile (sFile), sDFolder, True

End Sub

edited Apr 12 '15 at 21:19

answered Apr 12 '15 at 16:32

Byron Wall

3,970
2
13
29

Hi, thank you very much for coming back to me with this. Forgive, just so I don't make a mistake could you tell me please how I would integrate this with the hyperlink click? Kind Regards – IRHM Apr 12 '15 at 16:49
If you want to keep the hyperlinks, you can use the Worksheet event `FollowHyperlink` instead of the `SelectionChanged`. See edit above. – Byron Wall Apr 12 '15 at 21:16
Thank you very much for this. I've tried to incorporate the two scripts inserting `Call ListFilesInFolder` after this line in your code: `Dim sDFolder As String`, but unfortunately the file still opens rather than the 'Save' dialog. My apologies for being a little dense here, but could you possibly tell me where I've gone wrong. Kind Regards – IRHM Apr 13 '15 at 15:40
The follow hyperlink code should be added as a Worksheet event and not pasted into the middle of existing code. It is possible that the Hyperlink will open the file before the event code is executed. If that's the case, you need to remove the hyperlink and rely on a different event (like SelectionChanged). – Byron Wall Apr 13 '15 at 15:47
Hi @Byron Wall, thank you for this.I've manged to get the dialog box to open, and you say the file opens then the dialog window appears. The issue I have is that the dialog only gives the option to open a folder and not to save the file? Have you any ideas pleaser. Kind Regards. Chris – IRHM Apr 14 '15 at 09:07
Once you pick the folder, it copies the file to that folder. – Byron Wall Apr 14 '15 at 12:34
Hi, thank you for replying. As per your appreciated help, I followed the folder path through and selected the 'Open' button, but when I did this I received the erro message 'File Not Found' and debug highlighted this line as the cause: `FSO.CopyFile (sFile), sDFolder, True` Many thanks and kind regards Chris – IRHM Apr 15 '15 at 06:33

VBA Save File As

1 Answers1