Search folder -> find excel in folder and copy data

[Kasperj]

Hi guys

I have a problem. :crying: I need to make vba macro which can open a dialog to select a folder. Then I need the vba code to look in the folder and extract data from the excel files in the folder to my workbook. After I've imported the data from the excel files I need the code to move the excel files to an archive folder.

I hope you can help me

-Kasper

[snb]

Did you try this forum's search facilities ?

[Kasperj]

Yes, but I cannot figure out how to combine the aswers from the other posts. If the codes even can be combined..

[Kenneth Hobs]

Welcome to the forum!

What you have asked is essentially 3 questions. If you ask 3 rather than 1 big question, you get answers more quickly. If 1 pertains to 2, you can always included a link to the 1st.

For question 2 where you extract data, we have no idea what data you want extracted, copied, etc.

For question 3 where you want to move the the file to an archive folder, we have no idea what folder to move it to. One also needs to decide what to do if there is already a like-named file in the archive folder.

For question 1, let's do that first:

Sub test_GetFolder() MsgBox Get_Folder(ThisWorkbook.path, "Folder Picker")
End Sub

Function Get_Folder(Optional FolderPath As String, _

Optional HeaderMsg As String) As String
With Application.FileDialog(msoFileDialogFolderPicker)
If FolderPath = "" Then
.initialFilename = Application.DefaultFilePath
Else
.initialFilename = FolderPath
End If
.Title = HeaderMsg
If .show = -1 Then
Get_Folder = .SelectedItems(1)
Else
Get_Folder = ""
End If
End With
End Function




Now that question one is answered, for question 2, all Excel file types xls, xlsx, xlsm, etc.? Read or copy cells? Which ones? What sheets to read or copy from? For each "Excel" file, are they just in the parent folder chosen in question 1 or could they also be in subfolders of the parent folder?

As you can see, seemingly simple questions that you asked, begs for other questions to be answered.