Results 1 to 2 of 2
2013-02-27, 11:52 #1
- Join Date
- Dec 2012
- Thanked 0 Times in 0 Posts
Open a foler then select the correct filr and copy form that to your master
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)
2013-02-27, 13:42 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,511 Times in 1,372 Posts
This should do what you want.
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
You can comment out the MsgBox(es) if they were just for testing
Post back and let me know how it goes.