Excel VB editor Macro?

[Kanecnc]

So this is a side project I don't have to do for work but would make my life a lot easier. So I have been doing C++ for 3 yrs now but I haven't touched VB in the last 4 yrs.

Anyway my side task is I want to create a Excel VB Macro (I don't have any other option so it has to be a VB Macro) that when ran opens a text file and searches the text file for certain strings. I have been searching for starter code on Google that would perhaps jog my memory as well as provide a framework for what I want to accomplish. So my question is would this be hard to accomplish in the Excel VB editor, it seems to just be a simple text parsing problem, but every time I find code its seems over complicated and doesn't really do anything that would help me. Any help is very appreciated.

 

[Kanecnc]

Another piece of the program is it will take the strings found and import them into an excel sheet.

 

[pxc]

The VBA code will be simple. You use the FileSystemObject to open and read from the text file. Here's a simple example: http://www.ozgrid.com/forum/showpost.php?p=319450&postcount=2

Then you use WorkBook/WorkSheet objects or collections to fill in the cells. See examples of manipulating cells and sheets here: http://www.mvps.org/dmcritchie/excel/sheets.htm

The stuff sometimes looks overly complex because Office 2003 and earlier VBA is so primitive. It's really not hard to pick up though.

 

[CreganTur]

It sounds like a good automation program would suit your needs.

I would HIGHLY reccomment using vTask Studio, developed by Vista Software (absolutely no affiliation with MS Vista). Here's there website: http://www.vista-software.com/index.php

You can download a full version of the program to try for free for 30 days.

You don't have to learn a new language to use vTask Studio- it's an incredibly easy to use system that is unbelievably flexible and powerful. Plus the community over there is GREAT- you can get a rediculous ammount of help for any problem you might have with the program (or trying to get the program to do what you want)

I have used it to automate a number of programs that work with Excel- they actually have a whole block of tools that are specially designed to automate Excel.

 

[blanck]

This shouldn't be too hard. The task can be broken up into several components/functions:

-Extract the raw text from a file. FileSystemObject makes this really easy. Look up the OpenTextFile method.
-Extract your interesting words from the raw text. Given the TextStream from the previous function, this could return an array or Collection of interesting strings, or an array of more complex data structures, if you so wish. Look up the RegEx object to help with extracting interesting strings.
-Insert the strings into a spreadsheet. I won't go into Excel's object model, but you should be able to iterate over the rows/columns of a new or existing worksheet and insert your interesting strings.

 

[Kanecnc]

Thanks for all the feedback, I will definitely look into all the suggested solutions!