Excel and or Frontapge Macro

Posted on 2007-10-09
Last Modified: 2013-12-24
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.
Question by:jansson17
    LVL 23

    Expert Comment

    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:
    LVL 45

    Expert Comment


    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.

    LVL 45

    Expert Comment


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

    LVL 45

    Accepted Solution


    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

    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
    End Sub

    Open in new window


    Author Comment

    LVL 45

    Expert Comment

    jansson17 - Thanks for the grade - Patrick

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Having worked on larger scale sites, we found out that you are bound to look at more scalable solutions to integrating widgets, code snippets or complete applications and mesh them into functional sites, in any given composition. To share some of…
    This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now