Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro Open One File at a time

    I have a workbook that currently opens all files in a directory and sub-directory called C:\reports based on the file names in Col A on Sheet1

    I would like the macro amended as follows:

    1) If I double click on the File name in Col A (sheet1) , the appropriate file will be opened

    2) If I double click in Column B next to the file name in Column A , the appropriate file will be saved and closed


    Your assistance in writing the code is most appreciated
    Attached Files Attached Files

  2. #2
    New Lounger rollis13's Avatar
    Join Date
    Sep 2014
    Location
    Cordenons
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    I worked around your original macro so have a try with this macro to be placed in the sheet's (Sheet1) vbe module:
    Code:
    Option Explicit
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
        Dim fPATH As String:    fPATH = "C:\reports\"
        Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
        Dim FLD As Object:      Set FLD = FSO.GetFolder(fPATH)
        Dim SubFLDRS As Object: Set SubFLDRS = FLD.SubFolders
        Dim SubFLD As Object
        
        Cancel = True        'no editing mode
        Application.ScreenUpdating = False
        
        If Not Intersect(Range("A:A"), Target) Is Nothing Then
            On Error Resume Next
            For Each SubFLD In SubFLDRS
                Workbooks.Open (SubFLD & "\" & Target.Value)
            Next SubFLD
        End If
        
        If Not Intersect(Range("B:B"), Target) Is Nothing Then
            On Error Resume Next
            Workbooks(Target.Offset(0, -1).Value).Save
            Workbooks(Target.Offset(0, -1).Value).Close
        End If
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
    End Sub
    Last edited by rollis13; 2016-02-22 at 17:06.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated

  4. #4
    New Lounger rollis13's Avatar
    Join Date
    Sep 2014
    Location
    Cordenons
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Glad I was able to help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •