Help!! Need a Macro to perform basic file operations

Macrobeg

New Member
Joined
Apr 30, 2013
Messages
1
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!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Jan 11, 2013
Messages
618
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:
Code:
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:
</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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top