[newbie123]

Hello Everyone,

This is my first post on this site. I am also working on my first macro and it is turning out to be quite complicated but the mere idea of it working is enough to keep me at it.

In short, I want to use an excel macro to read a text file for a specific line of text, and then write data to the text file on the line before. In other words, rather than the data being added to the end of the file it's added to a particular section of the file after the existing data of that section.

Is this possible? Does anyone have example code for this? I have searched online and tried another forum and no luck so far. I appreciate any tips or sample code, thankyou!!

[Jimmy the Hand]

Hi and
Welcome to TSG!

Let me suggest something and then you say if I got it right or not. It's an algorithm.

1. Read a line from textfile "A".
2. Write the same line to a temporary textfile "B"
3. repeat steps 1 and 2 until you reach the specific line in textfile "A".
4. Write the additional data into textfile "B"
5. repeat steps 1 and 2 until you reach the end of textfile "A"
6. Kill (delete) textfile "A"
7. Rrename "B" to "A"

How does it sound?

Jimmy

[Rollin_Again]

Please post sample text files so we can see where and how you want to move the data.

Regards,
Rollin

[newbie123]

Jimmy the Hand,

I understand your method and that is certainly a possibility. My only concern is that i want to write to all of the sections of the file at the same time, that is, the user inputs "A", and the macro writes "B" to section 1, "C" to section 2, "D" to section 3, and so on. I suppose I could set it up such that it just rewrites the file in the manner you suggest 6 times (or as many sections as I need to write to). Perhaps this is the method to use.

Rollin_Again,

Please find attached the "template" file as I call it. You'll see there are several sections that will be filled with data. Here is the basic layout the program requires input:

Joint Commands that generate joints in 3-D space.

Member Commands that generate members between these joints in 3-D space.

Group Commands that make groups of the members for convenience (i.e. group "IBEAM" is a group that contains all of the members that are I-beams).

etc...

I have already written the macro to generate all of the commands for each section based on just two inputs from the user. The problem as stated is how to write this to a text file. Keep in mind I cannot do a batch generation, that is, i cant write all these to separate files and then combine later because sometimes the user has a big file and is just adding one or two more members. Therefore, it needs to be able to append to the document and not alter its previous contents.

If either of you need further explanation or anything else please let me know, thanks so much!!!

[Jimmy the Hand]

Hi Newbie,

I don't think my method should be repeated 6 times. All section inserts could be done in one run. The only thing needed would be to provide section names and the corresponding texts to insert.

But I've got another idea, that could be even more simple. Open the textfile in Excel, so that each line gets into one cell in column A. Insert as many rows as you want, where you want, and then, if ready, save the text file again. This little code opens your textfile, inserts 2 rows before Row 15, puts the string "Newly Inserted Text" into A15 and saves the textfile again.

Sub InstertIntoTextfile()
    Workbooks.OpenText Filename:="C:\LeadShieldingTemplate.txt", StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 2)
    Rows(15).Insert shift:=xlDown
    Rows(15).Insert shift:=xlDown
    Range("A15") = "Newly Inserted Text"
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

HTH

Jimmy

[newbie123]

Jimmy the Hand,

That seems like its going to do the trick, however, to make it do what i really want, when it inserts the text file into excel i can do a search for a particular phrase in the cells (basically the line of text after where i want to insert my new text) rather than predefining the position as this position changes everytime new text is added. Does this sound right?

Thank you so much for your help on this, I'll have to play with it a little more to adapt to my macro but I think you solved my problem!!

[Jimmy the Hand]

Originally Posted by newbie123

when it inserts the text file into excel i can do a search for a particular phrase in the cells (basically the line of text after where i want to insert my new text) rather than predefining the position as this position changes everytime new text is added. Does this sound right?

Absolutely so. I was thinking the same. I only drew up a draft of an idea, and counted on your being able to figure out how to find the sections
Sorry for not being specific enough. In case you need more help, pleas let us know.

I think you solved my problem!!

You think wrong
The only one who can solve your problems is the same one that causes them. And that's yourself. Help is all around, and asking for it is part of the solution. The unfortunate thing is that many people never reach the point where they would ask...

Cheers

Jimmy