VBA and taking specific String of text in multiple text type files and importing it into Excel to capture data

[Jsommers]

Ok,
So I am truly new at posting on chat boards and stuff like this plus I am a BRAND NEW VBA user but I thought I could expedite some of the things I do for my current job by trying to learn and right a Macro but now I am failing...... I have spent two days trying to figure this out and can not so I hope someone could help me out. Basically what I am trying to do is take a text type file with a .IES extension and bring only certain information of it into set cells, I have that figured out but what I want to achieve is taking a folder with multiple files of the same extension and bring in the same set information of all files into excel. I have been at this for two days and NO ONE has been able to help I have listed below the first one that I did, and got it to work but when I try to write a for loop I get messed up. I KNOW FOR A FACT this is probably super simple and I truly do understand what i need for this, but I just cant figure out how to do it.

_______________________________________________________________________________

This worked but I need to read an entire directory not just individual files and have spent countless hours being stuck!!

Private Sub CommandButton1_Click()

Dim LTotal As Integer
Dim iesFile As String
Dim text As String
Dim TextLine As String
Dim posLUM As Integer
Dim posWATT As Integer


iesFile = "C:\Users\som76059\Desktop\Work\LIGHTING\COMPLETED\VAP\IES\VAP_4000lm_FST_MD_XX_GZ10_35K_90CRI.ies"


Open iesFile For Input As #1 ' Open file.
Do Until EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
text = text & TextLine ' Print to the cells assigned.
Loop
Close #1
posLUM = InStr(text, "[_TOTALLUMINAIRELUMENS]") ' Text string looked for.
posWATT = InStr(text, "[_LAMPWATTAGE]") ' Text string looked for.
Range("A1:A999999").Value = Mid(text, posLUM + 24, 6) ' cells assigned.
Range("B1:B999999").Value = Mid(text, posWATT + 15, 5) ' cells assigned.





Application.ScreenUpdating = True
End Sub

_____________________________________________________________________________________


This was my attempt at the second portion of adding a loop to call back information that failed!!!!!



Private Sub CommandButton1_Click()

Dim MyFolder As String
Dim MyFile As String

MyFolder = ("C:\Users\som76059\Desktop\Work\LIGHTING\COMPLETED\VAP\IES") 'Folder Location
MyFile = Dir(MyFolder & "\*.ies") 'File Type
Dim iesFile As String
Dim currentrow As Integer: currentrow = 2


Do While MyFile <> "" 'This will go through all files in the directory, "Dir() returns an empty string at the end of the list

iesFile = MyFolder & "" & MyFile 'concatinates directory and filename

Open iesFile For Input As #1 ' Open file.


Do Until EOF(1) ' Read ies file line by line
Line Input #1, TextLine ' Read line into variable.
text = text & TextLine ' Print to the cells assigned.
If TextLine = "" Then 'error handler, if line was empty, ignore
Else
Dim splitline() As String
splitline() = Split(TextLine, "=", -1, vbTextCompare)
'because of how my specific text was formatted, this splits the line into 2 strings. The Tag is in the first element, the data in the second


If IsError(splitline(0)) Then
splitline(0) = ""
End If

Select Case Trim(splitline(0)) 'removes whitespace

Case "MANUFAC"
currentrow = currentrow + 1
ActiveSheet.Range("A" & currentrow).Cells(1, 1).Value = splitline(1)
Case "TOTALLUMINAIRELUMENS"
currentrow = currentrow + 1
ActiveSheet.Range("B" & currentrow).Cells(1, 1).Value = splitline(1)
Case "LAMPWATTAGE"
currentrow = currentrow + 1
ActiveSheet.Range("C" & currentrow).Cells(1, 1).Value = splitline(1)
Case "LAMPTYPE"
currentrow = currentrow + 1
ActiveSheet.Range("D" & currentrow).Cells(1, 1).Value = splitline(1)


End Select
End If
Loop


Close #1

MyFile = Dir() 'reads filename of next file in directory
currentrow = currentrow + 1


Loop

End Sub