Help!! Need a Macro to perform basic file operations

[Macrobeg]

Hello,

I am a beginner to excel Macro programming and looking for some help. My requirement is as follows,

I have a text file that contains some data in no specific format i.e. the fields are not formatted with any fixed space or a delimiter. However, each row begins with a specific number (not a row number) that uniquely identifies the text that will follow it. I need a macro to "search" for this specific number and then print (into another file - say the output file) the rest of the line that follows it (or allow me to define the number of characters that I would like to be displayed). I need to do this for the entire file, so it needs to skip to the next match until it has reached the end of the file.

Am clueless of how to build this. Can the experts out there please advise/help?

Many thanks,
A beginner!

 

[oldbrewer]

sort the whole sheet by the number in column A - then use a helper column to flag the one or more rows starting with your number - it should then be possible to write a macro that looks for these flags and highlights the rows, copying and pasting to another sheet. Does this sound possible to you.

 

[James Snyder]

First learning tool would be to do the steps recording them as a macro. It will only get you started, but it lets you see methods, properties, and objects so that you know what to do Google searches on. An example of what it will not show you is if you select the cell you want to "find", it will show a cell selection, not a Range().Find.

Opening and writing files is pretty easy. To open a file to read, here is an example from Sean Chesire:

Dim FileNum As Integer
Dim DataLine As String  

FileNum = FreeFile() 
Open "Filename" For Input As #FileNum  
While Not EOF(FileNum)
     Line Input #FileNum, DataLine ' read in data 1 line at a time
     ' decide what to do with dataline,
     ' depending on what processing you need to do for each case 
Wend
<code>

Writing out to a file is just as easy. Essentially, you are "Printing" a line at a time to the file. Below is a sample from code I am wotrking on right now:

</code>    fullPath = savePath & "\" & folderName & "\" & "INSPECTION" & sendDate & ".TXT"
    nextFP1 = FreeFile()     ' Get the next free file number
    Open fullPath For Output As #nextFP1  ' Open for write

    If nextFP1 > 0 Then
        Print #nextFP1, probLine
    End If<code>

The biggest problem you will run into is searching for something in a text file. ADO does it, but it isn't an entry level tool. It would probably be better to loop through each line and handle each line in sequence than try to do a search. Make an attempt and post back to this thread as you run into problems.
</code>

 

[James Snyder]

By the way, is this X12 or some known formatted file type?