Import multiple .csv files with variable names

[gelen4o]

Hi,

This thread is the result of an older one, which started out as a problem with column headers but turned into a headache with csv files ......

I have a folder with .csv files that go back 12 and even more months in the past. Every day the system I work with will create 4 new files and save them in this pecific directory (folder path remains the same)

Example:

TPOS101617.csv.20171020061940
APOS101617.csv.20171017060948
LPOS101617.csv.20171017061635
TPOS101617.csv.20171017061945

This is the 16/Oct/17 and every next work day will have its "own" 4 files.

What I need to do is to instruct VBA to open the folderpath, fetch all 4 files for a given date and aggregate them up in Activeworkbook.Worksheets("Position Data") - pos.
Then I have another macro that will peform a calculation and save results within the very same workbook.
When this has been done I need excel to delete current data import the next 4 files repeating the process.

I started putting together some code based on another VBA i use but I ran into several problems:

- variable file names: I have all dates from 01/01/2017 to 31/12/2017 listed in this exact format in a range in "assum". I'm want to use a look with the idea being: if date in assum.cell(u, "D") matches "FileDate", then import. However, I'm not quite sure if VBA will import all 4 files or only the first one it comes across
- WorkBook.OpenText this is something new to me and I simply don't know how to proceed with copying data from the .csv workbook and pasting it to my "Position Data" sheet.


here is what I managed to put together so far:

Sub FindFiles()
    
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Dim wbCSV As Workbook
  Dim objFSO, objFolder, objFile As Object
  Dim StartDate, FileDate As Date
  Dim Day, Month, Year, SoucePath, NameWorkbook As String
  
  Dim i As Integer
  Dim u As Integer
  
  Dim assum, pos As Worksheet
  
  i = 1
  u = 5
  
  
  Set assum = ActiveWorkbook.Worksheets("Assumptions")
  Set pos = ActiveWorkbook.Worksheets("Position Data")
  
        
  'create variable representing the name of the current Workbook
  NameWorkbook = ActiveWorkbook.Name
      
  'Read Start and End Date from Excel-Sheet
  StartDate = assum.Cells(u, "D")
  'Read Path from manual entry in Workbook
  SourcePath = "C:\Users\i936078\Desktop\Source Data\"
  
  'Create an instance of the FileSystemObject
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  
  'Get the folder object
  Set objFolder = objFSO.GetFolder(SourcePath)
 
        
  For Each objFile In objFolder.Files
  
                    If Mid(CStr(objFile.Name), 2, 3) = "POS" Then
                    Debug.Print Mid(CStr(objFile.Name), 2, 3)
                       
                         'construct date variable to check if the current file belongs to the relevant period as specified manually in Cells B3, C3
                          Year = Mid(objFile.Name, 9, 2)
                          Debug.Print Year
                          Month = Mid(objFile.Name, 7, 2)
                          Debug.Print Day
                          Day = Mid(objFile.Name, 5, 2)
                          Debug.Print Day
                          FileDate = Day & "/" & Month & "/" & Year
                          Debug.Print FileDate
                          Debug.Print StartDate
                          
                          
                          'check if the file belongs to the relevant period
                          If (FileDate = StartDate) Then
                          
                                  'change current directory to SourcePath directory
                                   ChDir SourcePath
                                   
                                   'open .csv as new workbook with just 1 sheet and copy data
                                   
                                   Workbooks.OpenText filename:=objFile, DataType:=xlDelimited, origin:=437, _
                                   TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                                   Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
                                   FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))
                                   
                                   
                          
                                   
 
                          End If
    
                     i = i + 1
                     u = u + 1
    
                  End If
        
        Next objFile
        
 
  
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
 
End Sub