Right now I have a macro that retrieves data from .chr files that are in the same folder as the excel workbook. It goes through the files and puts them in a worksheet in order by file name. I would like to put the data in the order of data modified
Dim myName, myPath, myName1, qt1
Dim counter, counter1 As Integer
Dim OK As Integer
Dim I As Integer
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "*.txt")
Worksheets(1).Activate
Worksheets.Add
Worksheets(1).Name = "merge__chr"
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
counter = 1
counter1 = 1
OK = 0
OK = InStr(1, myName, "chr.txt", vbTextCompare)
Do While myName <> ""
Do While OK = 0 And myName <> ""
myName = Dir
OK = InStr(1, myName, "chr.txt", vbTextCompare)
Loop
myName1 = "TEXT;" & myPath & myName
If OK <> 0 Then
With Worksheets("merge__chr").QueryTables.Add(Connection:=myName1, Destination:=Worksheets("merge__chr").Cells(counter1, 1))
If counter1 > 1 Then
.TextFileStartRow = 2
End If
.Refresh
End With
For I = 1 To 2
If Worksheets("merge__chr").Cells(counter1, 1) = "" Then
Set currentCell = Worksheets("merge__chr").Cells(counter1, 1)
currentCell.EntireRow.Delete
End If
counter1 = counter1 + 1
Next
counter1 = 1
Do While Worksheets("merge__chr").Cells(counter1, 1) <> ""
counter1 = counter1 + 1
Loop
End If
OK = 0
Loop
Do While Worksheets("merge__chr").Cells(counter, 1) <> ""
If Worksheets("merge__chr").Cells(counter, 1) = "END" Then
Set currentCell = Worksheets("merge__chr").Cells(counter, 1)
currentCell.EntireRow.Delete
End If
counter = counter + 1
Loop
Dim myName, myPath, myName1, qt1
Dim counter, counter1 As Integer
Dim OK As Integer
Dim I As Integer
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "*.txt")
Worksheets(1).Activate
Worksheets.Add
Worksheets(1).Name = "merge__chr"
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
counter = 1
counter1 = 1
OK = 0
OK = InStr(1, myName, "chr.txt", vbTextCompare)
Do While myName <> ""
Do While OK = 0 And myName <> ""
myName = Dir
OK = InStr(1, myName, "chr.txt", vbTextCompare)
Loop
myName1 = "TEXT;" & myPath & myName
If OK <> 0 Then
With Worksheets("merge__chr").QueryTables.Add(Connection:=myName1, Destination:=Worksheets("merge__chr").Cells(counter1, 1))
If counter1 > 1 Then
.TextFileStartRow = 2
End If
.Refresh
End With
For I = 1 To 2
If Worksheets("merge__chr").Cells(counter1, 1) = "" Then
Set currentCell = Worksheets("merge__chr").Cells(counter1, 1)
currentCell.EntireRow.Delete
End If
counter1 = counter1 + 1
Next
counter1 = 1
Do While Worksheets("merge__chr").Cells(counter1, 1) <> ""
counter1 = counter1 + 1
Loop
End If
OK = 0
Loop
Do While Worksheets("merge__chr").Cells(counter, 1) <> ""
If Worksheets("merge__chr").Cells(counter, 1) = "END" Then
Set currentCell = Worksheets("merge__chr").Cells(counter, 1)
currentCell.EntireRow.Delete
End If
counter = counter + 1
Loop