Excel and or Frontapge Macro

I need to create a Macro. Not sure if this would be done in Excel or Frontpage: The macro needs to be able to do the following(or something close)
1. Scan a pre-determined folder or folder structure and open up the files located within the specified folder in notepad.

2. Perform a search within each file within the specified folder and look for the following text within the code of each page: Date Submitted: (then the next 10 characters) The file names and Dates Submitted (and 10 characters) would then be dumped into a .xls this is very advanced for me and I cant really find anything that comes close.
jansson17Asked:
Who is Participating?
 
patrickabCommented:
jansson17,

Attached is a file with a macro to do the work. To get it to function you need to:

1. Place ONLY the text files in a subdirectory (folder) of their own
2. Insert the full PATH to that folder in the code - it's commented
3. Save the file and run the macro

I wrote a macro, asked for it to be corrected here on EE, but instead zorvek wrote a new macro - so all due recognition to him for this solution.

Hope it helps

Patrick
Sub ExtractData()
 
    Dim FilePath As String
    Dim FSO As Object
    Dim FSOFolder As Object
    Dim FSOFile As Object
    Dim TextStream As Object
    Dim FileData As String
    Dim Value As String
    Dim RegEx As Object
    Dim Row As Long
   
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.IgnoreCase = True
    RegEx.Pattern = "^[\S\s]*Date Submitted\: ([0-9]+)[\S\s]*$"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'put the full Path to the folder containing the text files in the next line
    Set FSOFolder = FSO.GetFolder("C:\Documents and Settings\Patrick & Thuc-Nghi\My Documents\Patrick's\Excel files\TextFiles")
    For Each FSOFile In FSOFolder.Files
        If Application.CountIf(Sheets("Database").[A:A], FSOFile.Name & "|*") = 0 Then
            Set TextStream = FSOFile.OpenAsTextStream(1, -2) ' OpenAsTextStream parameters: Reading, Default Format
            ' Use following if non-unicode data
            'FileData = StrConv(TextStream.ReadAll, vbUnicode)
            ' Use following if unicode data
            FileData = TextStream.ReadAll
            Value = RegEx.Replace(FileData, "$1")
            If Len(Value) > 0 Then
                Row = IIf(ThisWorkbook.Sheets("Database").UsedRange.Address = "$A$1", 1, ThisWorkbook.Sheets("Database").UsedRange.SpecialCells(xlLastCell).Row + 1)
                ThisWorkbook.Sheets("Database").Cells(Row, 1).Resize(1, 2) = Array(Join(Array(FSOFile.Name, Date, Time), " ¦¦ "), Value)
            End If
        End If
    Next FSOFile
    ThisWorkbook.Sheets("Database").Columns("A:B").AutoFit
 
End Sub

Open in new window

TF1.txt
jansson17-01.xls
0
 
basicinstinctCommented:
You should have posted this in the Visual Basic zone instead of Javascript.

It is definitely possible to do what you say as an Excel macro.  Most of the work would be done using the FileSystemObject.  Assuming you have some experience with programming you should be able to adapt sample code you find.

Here's a link to get you started:
http://www.excelsig.org/VBA/FileSystemObject.htm
0
 
patrickabCommented:
jansson17,

I understand your requirement but what I do not understand is why you want to use NotePad at all. It seems irrelevant to me in this context. Excel can open a text file, examine it and extract whatever is needed, record it in an Excel worksheet with the file name, and move onto the next file in the subdirectory. You don't need to use Java, or pure VB, just Excel's native VBA.

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
patrickabCommented:
jansson17,

Please upload 2 text files, which are typical of what you you've got. 'Sanitise' them if needs be, but do please upload them.

Patrick
0
 
jansson17Author Commented:
7
0
 
patrickabCommented:
jansson17 - Thanks for the grade - Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.