Posts by thelukeee

Sub LoopThroughTextFiles()
' Defines variables
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim Text As String
Dim Textline As String
Dim lastrow As Long
Dim colcount As Long
' Optimize Macro Speed

Application.ScreenUpdating = False
Application.EnableEvents = False

' Defines lastrow as the last column of data based on row 1
lastrow = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

' Sets the folder containing the text files
myPath = "\\hsmpcs01\HCTMFG\FURNACE\AtmFurnace\Run Reports" & "\"
' Target File Extension 

myExtension = "*.txt"

' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

StartDate = Worksheets("Intro").Range("B5")
EndDate = Worksheets("Intro").Range("B6")

' Loop through each text file in folder
Do While myFile <> ""

' Sets variable "colcount" To 1
colcount = 1

' Sets variable "Text" as blank
Text = ""

' Set variable equal to opened text file
Open myPath & myFile For Input As #1

lastModifiedDate = FileDateTime(myFile)

If lastModifiedDate >= StartDate And lastModifiedDate <= EndDate Then
        ' Do until the last line of the text file
        Do Until EOF(1)
            ' Add each line of the text file to variable "Text"
            Line Input #1, Textline
            Text = Textline
            ' Update colcount row of the current last column with the content of variable "Text"
            Cells(lastrow, colcount).Value = Text
           ' Increase colcount by 1
            colcount = colcount + 1
        Loop

        ' Close the text file
        Close #1

        ' Increase lastrow by 1 to account for the new data
        lastrow = lastrow + 1
        ' Get next text file name

        myFile = Dir

        Else
            Close #1
        End If
        Loop

ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True
' Message Box when tasks are completed
MsgBox "Task Complete!"

End Sub

Sub LoopThroughTextFiles()
' Defines variables
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim Text As String
Dim Textline As String
Dim lastrow As Long
Dim colcount As Long




' Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False




' Defines lastrow as the last column of data based on row 1
lastrow = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the folder containing the text files
myPath = "\\hsmpcs01\HCTMFG\FURNACE\AtmFurnace\Run Reports" & "\"




' Target File Extension (must include wildcard "*")
myExtension = "*.txt"




' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


StartDate = Worksheets("Intro").Range("B5")
EndDate = Worksheets("Intro").Range("B6")




' Loop through each text file in folder
Do While myFile <> ""
' Sets variable "colcount" To 1
colcount = 1
' Sets variable "Text" as blank
Text = ""
' Set variable equal to opened text file
Open myPath & myFile For Input As #1


lastModifiedDate = FileDateTime(myFile)


If lastModifiedDate >= StartDate And lastModifiedDate <= EndDate Then
' Do until the last line of the text file
Do Until EOF(1)
' Add each line of the text file to variable "Text"
Line Input #1, Textline
Text = Textline
' Update colcount row of the current last column with the content of variable "Text"
Cells(lastrow, colcount).Value = Text
' Increase colcount by 1
colcount = colcount + 1
Loop
' Close the text file
Close #1
' Increase lastrow by 1 to account for the new data
lastrow = lastrow + 1
' Get next text file name
myFile = Dir
Else
Close #1
End If
Loop




ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True


' Message Box when tasks are completed
MsgBox "Task Complete!"




End Sub