export data from multiple files into a single file

Hello graeme00,

Before I can help you with this, I need a little more information.

• Are there multiple days in a csv file?
• Do you need an InputBox to get the day you want to find?
• Is the data in the csv file in a single column with multiple rows?
• Can you attach a copy of one of these csv files for reference?

Hello graeme00,

The attached workbook contains a button on "Sheet1" to run the macro shown below. First, you will be prompted to enter the exchange date. It will open all CSV files in the directory "C:\Data\FOREX" and copy the data from the given date to "Sheet1" starting in cell "A1". The file name along with the data will be displayed in column "A". A blank line separates the data from each workbook.

Sub ImportData()

  Dim Data As Variant
  Dim FilePath As Variant
  Dim Item As Variant
  Dim n As Integer
  Dim oFolder As Object
  Dim oShell As Object
  Dim Rng As Range
  Dim sDate As Variant
  Dim Wks As Worksheet
  
    
    FilePath = "C:\Data\FOREX"
    
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A1")
    
      sDate = InputBox("Please enter the exchange date below as dd/mm/yyyy.")
      If IsBoolean(sDate) Or Not IsDate(sDate) Then Exit Sub
      
      Wks.UsedRange.Clear
      
      Set oShell = CreateObject("Shell.Application")
      Set oFolder = oShell.Namespace(FilePath)
      
        For Each Item In oFolder.Items
          If UCase(Item.Name) Like "*.CSV" Then
             n = FreeFile
             Open Item For Input Access Read As #n
               Do While LOF(n) <> EOF(n)
                 Line Input #n, Data
                 Data = Split(Data, ",")
                 If Data(0) = sDate Then
                    Rng.Offset(R, 0).Value = Item.Name
                    Rng.Offset(R + 1, 0).Resize(5, 1).Value = WorksheetFunction.Transpose(Data)
                    R = R + 7
                    Exit Do
                 End If
               Loop
             Close #n
          End If
        Next Item
        
End Sub

Hello graeme00,

Change the code line from this...

    If IsBoolean(sDate) Or Not IsDate(sDate) Then Exit Sub

To this...

    If sDate = "' Or Not IsDate(sDate) Then Exit Sub