FileCopy change folder destination (1 Viewer)

[SimoneRene]

Does anyone know of a way I can change the destination (targetfile) in my code so the user can choose the target file location each time. Currently its set to one folder location.
I thought about a msoFolderPicker but I dont know how to integrate it into my existing code.

Private Sub SaveNewIssBtn_Click()
Dim CopyDialog As office.FileDialog
Dim SourceFile As Variant
Dim targetFile As Variant
Dim varFile As Variant
Dim objDialog As Object
Dim Response As Integer
 FileViewerTxt = "" 'Text box blank
targetFile = "X:\Folder\Doc Folder\"   'Add File Destiation
ChDir Dir("*.*", vbDirectory)
Set CopyDialog = Application.FileDialog(msoFileDialogFilePicker)        'Open file picker and user chooses file to copy
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)         'Open file picker and user chooses file to display in text box (the copy and display occur in the same action so in reality the user only see's one file picker)
With CopyDialog
With objDialog
.AllowMultiSelect = False       'User can only select one file, for multiple files change to true
.Title = "Select File"          'selects the file name from the whole file path
.Filters.Add "pdf File", "*.pdf"        'Adds file type(pdf) to the end of the file name so in table "documentname.pdf" is shown
If .Show = False Then                   'no file selected and file picker closed
    MsgBox "File Upload Canceled"       'User didn't select a file so message box shows
    FileViewerTxt = Null                'Sets Textbox that displays file to null so that record can't be saved as file upload required(see saveBtn_Click()
End If
For Each SourceFile In .SelectedItems       'Search targetfile(file destination) for selected file to make sure user isn't duplicating a file
If Len(Dir(targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\")))) <> 0 Then         'If there is a match then...
    MsgBox "A Document with the same File Name has already been Issued, Please Rename and Try Again!"       'Display message box alerting user of duplicate file and cancel upload
    Exit Sub
    
 End If
 
Response = MsgBox(Prompt:="Are you sure you want to Save  " & SourceFile & "  in Issued Documents?", Buttons:=vbYesNo) 'Prompt user to confirm save
If Response = vbNo Then         'user answers no
MsgBox ("Upload Canceled!")     'message 'upload canceled'
FileViewerTxt = Null            'Sets Textbox that displays file to null so that record can't be saved as file upload required(see saveBtn_Click()
End If
If Response = vbYes Then        'User confirmed action to upload(Filecopy)
MsgBox ("Upload Successful!")    'Let user know upload was successful
    Me.FileViewerTxt = Dir(.SelectedItems(1)) 'Display file selected in textbox 'FileViewerTxt'
    
For Each varFile In .SelectedItems
    FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\"))       'Copy selcted file to target file(file destination defined above)
    Set objDialog = Nothing     'Reset object dialog
    
Next
End If
Next
End With
End With
End Sub

Thanks for any help!

 

[Tom_2012]

SimoneRene,

It depends if you want to change the target location each time you are running the macro or for each different file (in case you changed AllowMultiSelect =True)

For when you are running the macro, I usually do as below, which allows me to avoid modifying too much the code of the macro. It might not be the nicest thing but it easily does the trick.

Sub pathSite()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & ""
.Title = "Please select Destination folder"
.Show
SaveNewIssBtn_Click (.SelectedItems(1) & "")
End With
End Sub

Sub SaveNewIssBtn_Click(targetFile As String)

...


If you want to run it for each file, then you have to place it between these two rows

For Each SourceFile In .SelectedItems
...
If Len(Dir(targetFile & Mid$(SourceFile, InStrRev(SourceFile, "")))) <> 0 Then


However, you might have an issue with the AllowMultiSelect =True as you are reseting the object dialog while inside the loop checking for each dialog.SelectedItems.

The last rows
[/QUOTE=SimoneRene;1528100]
For Each varFile In .SelectedItems
FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, ""))
Set objDialog = Nothing 'Reset object dialog
Next
[/QUOTE]

shall be modified with just the one

FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, ""))

placing the "Set objDialog = Nothing" after the last Next.


Regards

Tom

 

[SimoneRene]

SimoneRene,

It depends if you want to change the target location each time you are running the macro or for each different file (in case you changed AllowMultiSelect =True)

For when you are running the macro, I usually do as below, which allows me to avoid modifying too much the code of the macro. It might not be the nicest thing but it easily does the trick.

Sub pathSite()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & ""
.Title = "Please select Destination folder"
.Show
SaveNewIssBtn_Click (.SelectedItems(1) & "")
End With
End Sub

Sub SaveNewIssBtn_Click(targetFile As String)

...


If you want to run it for each file, then you have to place it between these two rows

For Each SourceFile In .SelectedItems
...
If Len(Dir(targetFile & Mid$(SourceFile, InStrRev(SourceFile, "")))) <> 0 Then


However, you might have an issue with the AllowMultiSelect =True as you are reseting the object dialog while inside the loop checking for each dialog.SelectedItems.

The last rows
[/QUOTE=SimoneRene;1528100]
For Each varFile In .SelectedItems
FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, ""))
Set objDialog = Nothing 'Reset object dialog
Next

shall be modified with just the one

FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, ""))

placing the "Set objDialog = Nothing" after the last Next.


Regards

Tom[/QUOTE]

Thank you for the reply Tom. I might try this!

I have actually gone about it a different way and split it into two stages. The user uses one button to choose the location to save the file the chosen path is added to a text box(FolderTxt). There is then a separate button to choose the file and copy it. The destination is set to the text box FolderTxt.

Thanks again for your help!