Moving files between folders when folder names match partially (in R or VBA)

Question

I'm trying to solve the following problem

I have 9 folders titled PROS_2010 to PROS_2019. Each of them has about 500 subfolders with names structured as follows e.g. PROS_201001211_FIRM NAME_number. Each subfolder has a variety of pdf files with different names.

I have created in VBA another folder called sample with about 400 subfolders, each of which is named a specific FIRM NAME. For this I used the following code:

    Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub

I now want to move all the pdf files that are in the original subfolders PROS_201001211_FIRM NAME_number to the folders titled FIRM NAME only. Basically, each original subfolder contains a report about a firm for a specific year (2010 to 2019) and I want to get all the firm reports for all years in a single folder titled FIRM NAME

To make it easier I already have an excel file that basically has the complete list of subfolders that looks like this:

Data structure: Company name is the name of the folder in which I want to move the files that are currently in "attachment folder". attachment1 is the pdf file name (which always changes so ideally the code would pluck all the files in attachment folder and move them to the file with company name

Thanks in advance,

Simon

for instance, this question in R looks at a related problem https://stackoverflow.com/questions/14958516/looping-through-all-files-in-directory-in-r-applying-multiple-commands — SJDS, Feb 14 '20 at 04:20

score 0 · Answer 1 · answered Feb 14 '20 at 08:22

OK So thanks to the help of a mate I found it is super easy to solve this problem using the "command" command in windows

Basically create a text file (in notepad) that has the following structure

move "original pdf file directory" "new pdf file location\" ...

Repeat the structure for each file (which requires some basic excel string manipulations)

Then save the .txt file as a .cmd file and open it. Done

Moving files between folders when folder names match partially (in R or VBA)

1 Answers1