excel VBA - loop through files in each sub-folder and start with a specific file

[RCBricker]

Hello everyone,

I have a new project. I need to write a program that does the following:

1. User selects a folder
2. Code loops through the sub-folders of selected folder
3. Code loops through the files in the current sub-folder
4. Create a new excel file
5. starting with the "headerinfo" file
5A. import text into the new excel file
5B. loop through all of the remaining files in the sub-folder and import the text into excel file beneath the header info
6. save the new workbook to a specific file.

I began writing the following and then looked on line for the code further down.

Option Explicit

'for each file in folder
'   import to excel
'       start with header file
'       all other folders
'   Name each file
'   save in sub-folder
'   save on J:\ drive
'close new excel file
Public wbNEW As Workbook
Public wsSYS As Worksheet
Sub addwb()


Dim wb As Workbook, wbNEW As Workbook
Dim wsBUTTON As Worksheet, ws As Worksheet, wsSYS As Worksheet
Dim lngROW As Long, lngCOL As Long
Dim cell As Range, rng As Range
Dim varI As Variant, varJ As Variant
Dim strFOLDER As String, str As String

Set wb = ThisWorkbook
Set wsBUTTON = wb.Sheets("Button")

With Application.FileDialog(msoFileDialogFolderPicker)
   .AllowMultiSelect = False
   .Show
   On Error Resume Next
   strFOLDER = .SelectedItems(1)
   Err.Clear
   On Error GoTo 0
 End With
Call loopAllSubFolderSelectStartDirector(strFOLDER)

End Sub

I found the following code online, however I am not certain this is what I want and not sure how to change it to do steps 4-6.

Sub loopAllSubFolderSelectStartDirector(strFOLDER As String)

'Another Macro must call LoopAllSubFolders Macro to start to procedure
Call LoopAllSubFolders(strFOLDER)

End Sub
'List all files in sub folders
Sub LoopAllSubFolders(ByVal folderPath As String)

Dim fileName As String
Dim fullFilePath As String
Dim numFolders As Long
Dim folders() As String
Dim i As Long

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.*", vbDirectory)

While Len(fileName) <> 0

    If Left(fileName, 1) <> "." Then
 
        fullFilePath = folderPath & fileName
 
        If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
            ReDim Preserve folders(0 To numFolders) As String
            folders(numFolders) = fullFilePath
            numFolders = numFolders + 1
        Else
            'Insert the actions to be performed on each file
'This example will print the full file path to the immediate window
Debug.Print folderPath & fileName
        End If
 
    End If
 
    fileName = Dir()

Wend

For i = 0 To numFolders - 1

    LoopAllSubFolders folders(i)
 
Next i

End Sub

I have never tried to do something like this so not certain how to proceed.

any help would be appreciated.

Thanks,