0

I am writing a macro that needs to:

1 Get file list (around 10k rows) from a specific folder and subfolders of that folder and post it into an excel workbook (Sheet1) with File name and extension "somefile.ext" in column A and complete file path in column C (eg. D:\2014\Client Name\Misc\somefile.ext)

2 Filter out the files that meet my requirement and delete rows that do not.

3 use the complete path from column C to copy those listed files into a new directory but maintaining the subfolder structure so that:

D:\2014\Client Name\Misc\somefile.ext becomes D:\2015\Client Name\Misc\somefile.ext .

Where the path already exists (created with this macro) in the new folder but the file does not.

Now I have made it up to #3 on my own. I am stuck at copying those files, I simply lack the know-how. I am asking you guys for help.

Here is the code that works up to but not including point 3:

Option Explicit
Sub ListFiles()

Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.folder
Dim strTopFolderName As String

Range("A1").Value = "File Name"
Range("B1").Value = "File Type"
Range("C1").Value = "File Patch"

strTopFolderName = "D:\2014"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTopFolder = objFSO.GetFolder(strTopFolderName)

Call RecursiveFolder(objTopFolder, True)

Columns.AutoFit

End Sub

Sub RecursiveFolder(objFolder As Scripting.folder, _
IncludeSubFolders As Boolean)

Dim objFile As Scripting.file
Dim objSubFolder As Scripting.folder
Dim NextRow As Long

NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

For Each objFile In objFolder.Files
    Cells(NextRow, "A").Value = objFile.Name
    Cells(NextRow, "B").Value = objFile.Type
    Cells(NextRow, "C").Value = objFile.path
    NextRow = NextRow + 1
Next objFile

If IncludeSubFolders Then
    For Each objSubFolder In objFolder.SubFolders
        Call RecursiveFolder(objSubFolder, True)
    Next objSubFolder
End If
End Sub
Sub delete_rows()

Dim lastrow As Long
Dim row_index As Long

Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If InStr(Cells(row_index, "A").Value, "Processing") = 0 Then
Cells(row_index, "A").EntireRow.Delete
End If
Next
Columns.AutoFit
Application.ScreenUpdating = True
End Sub
user3889304
  • 15
  • 1
  • 5
  • 1
    If you post your existing code it will be much easier to make suggestions. Following your posted description it seems as though you just need to replace "\2014\" with "\2015\" to get the destination path ? – Tim Williams Jul 29 '14 at 21:34
  • Correct, also I need code that will actually go to original path, grab the file from subfolder "D:\2014\Client Name\Misc" and copy it into the new path D:\2015\Client Name\Misc and then go to another one on the list.. Also I updated the post with the code I have so far. As you can see i have not even begun to write the code for copying. – user3889304 Jul 29 '14 at 22:30
  • Copying a file in VBA is pretty basic: `FileCopy "D:\2014\Client Name\Misc\somefile.ext", "D:\2015\Client Name\Misc\somefile.ext"` – Tim Williams Jul 29 '14 at 23:36
  • This will work fine for one file (Right? )in this particular path but I have about 3K files, each under a different Client Name folder. Only constants are D:\2014\...\Misc and D:\2015\...\Misc . Also, all those paths are listed in an excel file (with Sub ListFiles() I used ). I am trying to understand how to make VBA use this excel list of paths to do the copying. I was thinking FileSystemObject but dont know yet how to do it. – user3889304 Jul 29 '14 at 23:45
  • You can loop over the cells in the range with the existing paths and for each cell call `FileCopy cell.Value, Replace(cell.value,"D:\2014\", "D:\2015\")` You don't even need the FSO for this: `FileCopy` is a native call in VBA. – Tim Williams Jul 29 '14 at 23:59
  • Thank you, I will try this ASAP. To be honest this has not even occurred to me, got fixated on complicating things again:) – user3889304 Jul 30 '14 at 01:43
  • [This might be of interest?](http://stackoverflow.com/questions/21277826/how-to-copy-files-to-another-folder-by-finding-the-location/21278717#21278717) This is what @TimWilliams is talking about I believe for item # 3 that is. But I would suggest to use the same approach to get 1 and 2 utilizing *DIR function* – L42 Jul 30 '14 at 02:35
  • @ L42, The first part of the code is good, it gets the path but it then copies the file to one specified Dir. I am looking for a way to copy the file to a sub so the path is exactly the same except the top directory is 2015 instead of 2014. – user3889304 Jul 30 '14 at 19:14
  • @ Tim Williams, Still playing with your suggestion, so far the loop has a problem where it does not recognize the path correctly. I will figure it out hopefuly... – user3889304 Jul 30 '14 at 19:16

1 Answers1

0

I think this will do what you want (You can remove the /K to make the command window go away).

  Call Shell("""cmd"" /K copy " & _
    "D:\2014\Client Name\Misc\somefile.ext " & _
    "D:\2015\Client Name\Misc\somefile.ext", vbNormalFocus)

EDIT: Tim's answer (as a comment) is much more straightforward. I was thinking that a "shelled" command could use wildcards, which may be useful and I don't think you can do that using FileCopy.

FileCopy source, destination

source: Required. String expression that specifies the name of the file to be copied. The source may include directory or folder, and drive. destination: Required. String expression that specifies the target file name. The destination may include directory or folder, and drive.

dcromley
  • 1,373
  • 1
  • 8
  • 23