Results 1 to 2 of 2
2016-03-08, 02:00 #1
- Join Date
- Mar 2016
- Thanked 0 Times in 0 Posts
Excel VBA to list and move all files in a directory
Good day Gurus,
I am working on a project for my own personal reference.
What I need the VBA to do is as follows (In 2 parts)
- Open windows folder picker
- Collect file names and file locations
- Populate column A with file path
- Populate Column B with File name
- Get Old directory From column A
- Check column C for new directory
- Take file from Column B and check if exists in new directory
- if it exists, move to a folder called "Duplicates" in the root of the folder path that was selected by windows folder picker
- If not, move to new directory
- Update Column D with new path
Code so far: Part 1
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim LastRow As Long
Dim fldr As FileDialog
Dim sItem As String
'Select Last used cell
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
'''''''Need a folder picker'''''''
Set fldr = Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
GetFolder = sItem
Set fldr = Nothing
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'print file name
Cells(i + 1, 2) = objFile.Name
'print file path
Cells(i + 1, 1) = objFile.Path
i = i + 1
I can however not get it to work.
2016-03-08, 10:33 #2
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 466 Times in 444 Posts
I previously posted a file to move files from one folder to another.
It uses a 'folder picker'.
Perhaps you could adapt this for your use.