johnbrownbaby
New Member
- Joined
- Dec 9, 2015
- Messages
- 38
I would like to copy specific files to specific folders using Excel VBA.
I obtained code that can allow the user to select the files and copy them to another folder as defined by the `msoFileDialogFolderPicker`. How to modify the code such that the respective file goes to the folder as defined in the excel sheet:
The folders already exist and are contained in the same main folder as the files. Here is the folder structure:
Here is the code for copying selected files to a folder:
How do I modify the code to copy the selected files to the respective folders as given in the excel columns? I would like to know how to get the xDPathStr by indexing the column number from the code above?
Please note that I have also posted this question on the stackexchange website but I have not gotten a favorable response.
Any Help will be greatly appreciated!
I obtained code that can allow the user to select the files and copy them to another folder as defined by the `msoFileDialogFolderPicker`. How to modify the code such that the respective file goes to the folder as defined in the excel sheet:
The folders already exist and are contained in the same main folder as the files. Here is the folder structure:
Here is the code for copying selected files to a folder:
VBA Code:
Sub movefiles()
'Updateby Extendoffice
Dim xRg As Range, xCell As Range
Dim xSFileDlg As FileDialog, xDFileDlg As FileDialog
Dim xSPathStr As Variant, xDPathStr As Variant
Dim xVal As String
On Error Resume Next
Set xRg = Application.InputBox("Please select the file names:", "BooBooMan", ActiveWindow.RangeSelection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xSFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
xSFileDlg.Title = " Please select the original folder:"
If xSFileDlg.Show <> -1 Then Exit Sub
xSPathStr = xSFileDlg.SelectedItems.Item(1) & "\"
'Set xDFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
'xDFileDlg.Title = " Please select the destination folder:"
'If xDFileDlg.Show <> -1 Then Exit Sub
'xDPathStr = xDFileDlg.SelectedItems.Item(1) & "\"
For Each xCell In xRg
xVal = xCell.Value
If TypeName(xVal) = "String" And xVal <> "" Then
FileCopy xSPathStr & xVal, xDPathStr & xVal
Kill xSPathStr & xVal
End If
Next
End Sub
How do I modify the code to copy the selected files to the respective folders as given in the excel columns? I would like to know how to get the xDPathStr by indexing the column number from the code above?
Please note that I have also posted this question on the stackexchange website but I have not gotten a favorable response.
Any Help will be greatly appreciated!