Filtering, selecting and importing text files on single Excel sheet

Question

Hey!

Does anybody can help me with Excel VBA macro code in order to import data from text files into single Excel spread sheet? I want to create User Form where user can select start and end date of interest and macro code will import bunch of text files depending on user demands...

My text files are named: 20130619004948DataLog.txt (meaning: yyyy mm dd hh mm ss). Text file contains recordings for each 15 seconds... It would be great to omit time tail (meaning that user can only specify date). Text files for one day of interest (I have text files covering whole year):

20130619004948DataLog.txt

20130619014948DataLog.txt

20130619024948DataLog.txt

20130619034948DataLog.txt

20130619044948DataLog.txt

20130619054948DataLog.txt

20130619064948DataLog.txt

20130619074948DataLog.txt

20130619084948DataLog.txt

20130619094948DataLog.txt

20130619104948DataLog.txt

20130619114948DataLog.txt

20130619124948DataLog.txt

20130619134948DataLog.txt

20130619144948DataLog.txt

20130619154948DataLog.txt

20130619164948DataLog.txt

20130619174948DataLog.txt

20130619184948DataLog.txt

20130619194948DataLog.txt

20130619204948DataLog.txt

20130619214948DataLog.txt

20130619224948DataLog.txt

20130619234948DataLog.txt

Option Explicit

Sub SearchFiles()
    Dim file As Variant
    Dim x As Integer
    Dim myWB As Workbook
    Dim WB As Workbook
    Dim newWS As Worksheet
    Dim L As Long, t As Long, i As Long
    Dim StartDateL As String
    Dim EndDateL As String
    Dim bool As Boolean
    
    bool = False ' to check if other versions are present
    
    StartDateL = Format(Calendar1, "yyyymmdd")
    EndDateL = Format(Calendar2, "yyyymmdd")
' I am using Userform asking user to select the date and time range of interet,
' However, I want to use only the date to filter the files having the name with that particular date
        
    file = Dir("c:\myfolder\") ' folder with all text files

' I need assistance with the following part:
'1) How to filter and select the files between StartDateL and EndDateL_
'(including files with that dates as well)?

    While (file <> "")
        If InStr(file, StartDateL) > 0 Then 'Not sure if the statements inside parenthesis is correct
            bool = True
            GoTo Line1:
        End If
        file = Dir
    Wend
Line1:
    If Not bool Then
        file = "c:\myfolder\20130115033100DataLog.txt" 'Just for a test that the code works as intended
    End If

'This part for the selected text files to be loaded on a single Excel Sheet.

    Set myWB = ThisWorkbook
    Set newWS = Sheets(1)
    L = myWB.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    t = 1
    
    For x = 1 To UBound(file)
        Workbooks.OpenText Filename:=file(x), DataType:=xlDelimited, Tab:=True, Semicolon:=True, Space:=False, Comma:=False
        Set WB = ActiveWorkbook
        WB.Sheets(1).UsedRange.Copy newWS.Cells(t, 2)
        t = myWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1
        WB.Close False
    Next
        myWB.Sheets(1).Columns(1).Delete
        Application.ScreenUpdating = False
        Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Answer 1

I thought you had this working a couple weeks ago.  What happened to that solution?

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.