PDA

View Full Version : Pick directory and merge 2 files into 1 workbook



Sandler
11-16-2017, 08:14 AM
Hi everyone,

I am looking for a VBA code that would allow me to pick a directory

(The files have the same name every month, but the folder changes every month)

then it would find the 2 files that have a specific string of text in the title (so wildcards are involved).

So for example, somewhere in the filename, it would have to have the following:

file 1: Cats-Are-Cute
file 2: Dogs-Are-Too

and merge those sheets into a new workbooks named analysis.

Sandler
11-16-2017, 08:32 AM
OK, I think I got the first part, the option to pick the Directory.


Sub GetFolder()
Dim FolderName As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
On Error Resume Next
FolderName = .SelectedItems(1)
Err.Clear
On Error GoTo 0
End With
End Sub

Sandler
11-16-2017, 02:51 PM
bump...

mancubus
11-17-2017, 07:43 AM
something to play with:



Sub vbax_61347_select_folder_open_2wbs_merge_into_1wb()

Dim fPath As String
Dim wb1 As Workbook, wb2 As Workbook

With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = -1 Then
fPath = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With

Set wb1 = Workbooks.Open(fPath & "*Cats-Are-Cute*.xls?")
Set wb2 = Workbooks.Open(fPath & "*Dogs-Are-Too*.xls?")

wb1.Worksheets("cat").Copy
wb2.Worksheets("dog").Copy After:=ActiveWorkbook.Worksheets(1)
'change cat and dog to suit

ActiveWorkbook.SaveAs fPath & "analysis.xlsx", 51 'save as new file in the same folder
'51 = xlsx, 52 = xlsm, 50 = xlsb, 56 = xls

wb1.Close False
wb2.Close False
'close previously opened files without saving them

End Sub

Sandler
11-19-2017, 06:25 PM
Thank you so much, Mancubus!

I will give it a try.