none
Filtering, selecting and importing text files on single Excel sheet RRS feed

  • 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
    Tuesday, February 10, 2015 11:47 AM

All replies

  • 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.

    Friday, February 20, 2015 1:24 PM