Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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.
0
jansson17
Asked:
jansson17
  • 4
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
jansson17Author Commented:
7
0
 
patrickabCommented:
jansson17 - Thanks for the grade - Patrick
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now