Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Open a foler then select the correct filr and copy form that to your master

    workbook.

    Hi All,

    I have some code that opens a directory and then allows the user to select the correct excel file thaey require.

    I would then want to Macro to reference the file they just opened i.e. not the hard coded name of Windows("Deal Test.xlsx").Activate and then use that to copy form.
    Currently the macro runs all at once so when the user selects thes correct file the macro has already finished.

    Probably an easy and stupid one so apolgies im advance.




    'Dim sPath As String
    '
    'sPath = "\\FILE01\CCA$\Management\Reporting\Dean's Reports"
    '
    'retVal = Shell("explorer.exe " & sPath, vbNormalFocus)

    Windows("Deal Test.xlsx").Activate
    Range("A2:G2").Select
    Selection.Copy
    Windows("Deal Template.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Range("A2").Select

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    This should do what you want.
    Code:
    Sub Main()
    
        Dim fd              As FileDialog
        Dim vSelectedItem   As Variant
        Dim vParts          As Variant
        Dim zBaseDir        As String
        Dim zFName          As String
        Dim zPath           As String
        Dim wkbOpened       As Workbook
        Dim wkbBase         As Workbook
        
        
        zBaseDir = CurDir()
        Set wkbBase = ActiveWorkbook
        ChDir ("\\FILE01\CCA$\Management\Reporting\Dean's Reports")
        
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
        With fd
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "Excel Files", "*.xl*"
            .Title = "Please select your Excel file."
            If .Show = -1 Then
    
                'Step through each string in the FileDialogSelectedItems collection.
                For Each vSelectedItem In .SelectedItems
                    vParts = Split(vSelectedItem, "\")
                    zFName = vParts(UBound(vParts))
                    zPath = Left(vSelectedItem, Len(vSelectedItem) - Len(zFName))
                    MsgBox "Path      = " & zPath & vbCrLf & vbCrLf & _
                           "File name = " & zFName, _
                           vbOKOnly + vbInformation, "Select File Informatioin"
                           
    
                Next vSelectedItem
            
            Else            'The user pressed Cancel.
              MsgBox "User exited dialog w/o making a selection...Exiting!", _
                     vbInformation + vbOKOnly, "No File:"
              Exit Sub
            End If
            
        End With
    
        'Set the object variable to Nothing.
        Set fd = Nothing
        
         Set wkbBase = Workbooks.Open(zPath & zFName, , True)
         'Note: Opening the file makes it active!
         
    '     your processing here
    
          Range("A2:G2").Select
          Selection.Copy
          wkbBase.Activate    'Change to paste sheet
          Range("A2").Select
          ActiveSheet.Paste
          CutCopyMode = False
          Range("A2").Select
          wkbOpened.Activate  'Back to copy source sheet
          
    '     end of your procession
    
          wkbOpened.Close
          
          
         
        ChDir (zBaseDir)
        
        'clean up code or what ever else you need to do.
        
    End Sub
    Note: I tested this on my directories for the all the code except that section marked of as your code here. I may have the workbooks reversed in the .Activate code but that's easily fixed.
    You can comment out the MsgBox(es) if they were just for testing

    Post back and let me know how it goes.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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