import data from more than one folder in one sub loop

[dantheman9]

Im having a bit of an issue, im trying to import two data text files (with both have some part with the same name), into tables in excel.
The simplest route been to process the first text file, then import and add the relevent text file with the same part name in from the other folder, then to loop onto the next file.


I have been struggling with the following ;

Public Sub DetectNewSplitFile()
 Const sFileSpec As String = "*" & "Splits Data" & "*" & ".txt"      ' type of file to watch
  Const sAgeSelect As String = "00:00:30"  ' ignore files newer than this
  Dim sFileName As String
  Dim dFileStamp As Date
  Dim iFiles As Integer
  Dim iNewFiles As Integer
  Dim dLastFileProcessed As Date
  Dim dLatestFileDetected As Date
  
  
    Dim sh As Worksheet, sPath As String, sName As String
Dim r As Range, fName As String
Dim ShtName1 As String
Dim ShtName As String
Dim NewSht As Worksheet
Dim str As String
 Userform1.Fname1.Caption = LText
  Userform1.NFiles.Caption = "Looking in Folder Please wait...."
Application.ScreenUpdating = False

ShtName = "TEMPS"
On Error Resume Next
Set NewSht = Sheets(ShtName)
On Error GoTo 0
If NewSht Is Nothing Then
    Set NewSht = Worksheets.Add
    NewSht.name = ShtName
End If
  
  
  dLastFileProcessed = lastDate
  'ThisWorkbook.Sheets("Sheet1").Range ("A1")
  
  sFileName = Dir(sFolder & sFileSpec)
  Do While sFileName <> ""
    dFileStamp = FileDateTime(sFolder & sFileName)
    If dFileStamp > dLastFileProcessed And dFileStamp < Now() - TimeValue(sAgeSelect) Then
      iNewFiles = iNewFiles + 1
      
[COLOR=lime]Code to process first file[/COLOR]   

   Close #1 [COLOR=lime]used to close file after processing[/COLOR]
 
Dim d As Integer
Dim rFilename As String
Dim replacename As String
Dim striped As String
d = numbers + 10
Dim rFname As String
Dim ShtNamet As String
Dim NewShtt As Worksheet
Dim stripedt As String
Dim resultclass As String
ShtNamet = "resultr"
On Error Resume Next
Set NewShtt = Sheets(ShtNamet)
On Error GoTo 0
If NewShtt Is Nothing Then
   Set NewShtt = Worksheets.Add
   NewShtt.name = ShtNamet
End If
resultclass = "*" & "Results" & "*" & ".txt"
[COLOR=red]rFname = Dir(sFolder & resultclass)  for testing all files are in the same folder, a cannot work out to set another String to replace sFolder, with rFolder, every time i try it sets them both to blank.[/COLOR]
 
[COLOR=lime]processing data from second text file and add to table [/COLOR]
     
 If dFileStamp > dLatestFileDetected Then dLatestFileDetected = dFileStamp
    End If
    iFiles = iFiles + 1
    [COLOR=red]sFileName = Dir()     issue here where sFileName changes the first part of the file name correctly, but still has the Results.txt string at the end.
[/COLOR]    
    ' testing
  Close #1
    Loop