Results 1 to 2 of 2
  1. #1
    New Lounger
    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)

    Part 1

    • Open windows folder picker
    • Collect file names and file locations
    • Populate column A with file path
    • Populate Column B with File name

    Part 2
    • 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

    Sub GetFileList()
    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
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Get the folder object
    '''''''Need a folder picker'''''''
    Set fldr = Application.FileDialog(msoFileDialogFilePicker)
    With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
    End With
    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
    Next objFile
    End Sub

    I can however not get it to work.

    Please assist.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 466 Times in 444 Posts
    Hi Gabushna

    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.

    Attached Files Attached Files

Posting Permissions

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