Hello Johny,
The following code doesn't depend on the filename list, but could be modified to do that if the approach here doesn't work (this is modified slightly from code I wrote recently, so it was easier to use this approach than starting from scratch). Also, this is a "first draft", in that it doesn't yet split your incoming data into multiple columns. That should be easy to add in...it depends on how your data is delimited or arranged in the incoming data file.
The following steps may sound tedious...I've given lots of detail since you're not very familiar with Excel or Visual Basic. It should be worth it given the number of files you're working with.
To use this, open or create a workbook to use to store the macro (for the time being, not the name of the file that will contain all of the data), then open a visual basic module...
Press Alt-F11 to open the visual basic editor
In the Project window (usually a pane on the far left top), highlight the workbook you're going to use to store the macro
From the VBA Insert menu, choose module
Paste the code below into the new module
Modify the file extension if it's not ".txt"
Change the path assignment to the location of your 1500 files (although for testing you might want to copy just a few to a test directory, and change the path to that location).
Switch back to the workbook / worksheet, select Tools, Macro, Macros, highlight GetHTMLData, and select run. A new workbook will be created that contains lines 11, 14, 16, 18, and 20 from each of the files in the target directory.
Once this part works for you, we can add in the details of the data layout or format (as written this macro will put it all in column A)
Code:
Sub GetHTMLData()
'Assumptions: file extension is .txt
'
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, a, FileToOpen
Dim datarow As Long
Dim fname
Dim ThisBook As String, Target_Sheet As String, nextline As String, ReportDate As String
Dim LineNum As Integer, RecordRow As Integer
Dim AcctCol As Integer, DescCol As Integer, ValuCol As Integer, CurrCol As Integer, DateCol As Integer, InfoCol As Integer
Dim CustCol As Integer, VendCol As Integer, PartCol As Integer, XxxxCol As Integer
Dim OrgCol As Integer, DstCol As Integer, CoCol As Integer, FlagCol As Integer, TypeCol As Integer
Dim DateStart As Integer, PageStart As Integer, PcsCol As Integer, WtCol As Integer
Dim f As String, Path As String
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="Summary.xls" 'change if desired
ThisBook = ActiveWorkbook.Name
Target_Sheet = ActiveSheet.Name
datarow = 1
Path = "C:\excel tests\" 'replace with your directory name here
ChDir Path
FileToOpen = dir(Path & "*.txt") 'if the file extension is something other than .htm, change this
While FileToOpen <> ""
LineNum = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile(FileToOpen, ForReading, False)
Application.ScreenUpdating = False
Do While a.AtEndOfStream <> True And LineNum <= 20 'keep going to the end of the file
nextline = a.readline 'get the line from the file
Select Case LineNum
Case 11, 14, 16, 18, 20
With Workbooks(ThisBook).Worksheets(Target_Sheet)
datarow = datarow + 1
.Cells(datarow, 1) = nextline 'change this to split the data into appropriate cells
'.Cells(datarow, 2) = mid(nextline,....)
End With
End Select
LineNum = LineNum + 1
Loop
a.Close
'save it
Workbooks(ThisBook).Save
Application.ScreenUpdating = True
FileToOpen = dir()
Wend
End Sub
It's quite late here, so I won't be able to respond until morning my time (not enough hours away at this point), but if this works, let me know how the data on the selected input lines is delimited or structured, and I can modify the middle parts of the code to split it into appropriate cells.
Hope this gets you started in the right direction,
Cindy
A big hug from me to you!
