VBA: Read cell number and write it in correct place in a .txt

[ropbasuel]

Hi all,

I need help in doing a VBA macro. I have one Excel file where I have from B1 to B100 cells with a number (Area). Each Area is of a place number. So I have:

A         B
1        100
2        300
3        150
..        ..

The .txt that I have is something like this:

SACR id 'sacr1' nm '2  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr3' nm '3  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr2' nm '4  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr10' nm '5  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr4' nm '6  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr20' nm '7  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr50' nm '8  ' ar "Area_here" ca '1' uh '1' op '1' ms '' aaf 1 sacr

As you can see each 'sacr1' .. 'sacr3', etc corresponds to the place number 1 and 3 in the Excel file.

So what I would like, it's a VBA macro that could read the place area in the Excel file and write it down in the perfect place in the .txt. For this example that I've write, it would be..

SACR id 'sacr1' nm '2  ' ar "100" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr3' nm '3  ' ar "150" ca '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr2' nm '4  ' ar "300" ca '1' uh '1' op '1' ms '' aaf 1 sacr

I don't know if I could express myself in the better way. If not please say so.

Thank you so much for anyone that tries to help me!

Regards

[Mallycat]

I suggest on a second sheet, create a formula that produces a single line of output you want. You will need to concatonate your text with the values. A simplistic example would be

="SACR id etc....." & b1 & "ca etc...."

You will need to use chr(34) to produce your double quotes.

Once you have the second page as you want it, save the book, turn on macro recorder, and then save the page as type *.txt. Turn off the macro recorder and edit the code as needed.

[ropbasuel]

Dear Mallycat,

Thanks for your answer. Sorry but I did not understand how to do what you have explained. Isn't there a way to to what I want with a macro? I will post here a code that I'm using to add text to a .txt a little bit similar with this, but that I don't know how to adapt it to this problem:

Sub AddStation()
    Dim i As Integer, tmp As String
    i = 1
    
    cPath = ThisWorkbook.Path & "\"
    
    Open cPath & "test.txt" For Input As #1
    Open cPath & "output.txt" For Output As #2

    Do Until EOF(1)
      Line Input #1, tmp
      If InStr(1, tmp, "''") > 0 Then
        tmp = Replace(tmp, "''", "'TEST" & i & "'")
        i = i + 1
      End If
      Print #2, tmp
    Loop
    
    Close #1
    Close #2
End Sub

Is there a way to use this kind of code to solve my current problem?

Thanks

[Mallycat]

There is always a way with VBA. Try this. Run it from your open sheet with the input data you describe.

Sub CreateTxtFile()
  
    cPath = ThisWorkbook.Path & "\"
    Open cPath & "output.txt" For Output As #1
    Range("A1").Select
    
    While Selection.Value <> ""
        Print #1, "SACR id 'sacr" & Selection & "' nm '4  ' ar " & Chr(34) & Selection.Offset(0, 1) & Chr(34) & " '1' uh '1' op '1' ms '' aaf 1 sacr"
        Selection.Offset(1, 0).Select
    Wend
    
    Close #1

End Sub

[ropbasuel]

Dear Mallycat,

Now we have some results :D But there are 2 problems:

1 - With this code all .txt lines are like this:

SACR id 'sacr1' nm '4  ' ar "150" '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr2' nm '4  ' ar "200" '1' uh '1' op '1' ms '' aaf 1 sacr
SACR id 'sacr3' nm '4  ' ar "250" '1' uh '1' op '1' ms '' aaf 1 sacr

This nm '4 should not be changed from the originals.

2 - The .txt file that exists has more text than this lines. With this code, the .txt is completely erased and only writes the data we want. Is there any way to maintain the original text and not to erase it all over?

Thank you so much, and sorry for all the work..

[Mallycat]

if you want to append data to an existing file, just change the second line of code from "For Output" to "For Append".

Regarding nm '4, where does this value come from? it is not the same as sacr1 and it doesn't seem to start from 1.

[ropbasuel]

Dear Mallycat,

With the "For Append" option, the code does not change my .txt but just add the lines to the .txt

Here I attach one part of the .txt that I want to be changed. I would like that automatically the VBA code changed the "Area_here" with the Area read in the Excel file. But as you can see, in the top of the .txt there are some text lines that I do not want to be deleted.

Regarding the nm'4 its a "code name". Each line has one of this "code name" and does not start from 1. Just imagine that is a random text number.

Thank you so much

test.txt

[Mallycat]

What you are asking for is changing each time you post. Now I see there are 3 lines at the top of the txt that are to be ignored, plus now the " are missing from the input file. It is difficult to help you without all the information. If you post an exact set of sample data spreadsheet and txt file, and an exact set of output, I can create the macro for you.