Read .xls data -> insert in .txt file

Hi All,
New user here. I routinely create input files (.txt format) for a hydrology program written in fortran, and I was wondering if how difficult it would be to create VBA code that would automate that process, bearing in mind that I know almost nothing about VBA. I have some background in C++, but nothing too extensive. Essentially, I want the program to read multiple rows in excel, then extract data from multiple cells in each row and insert the data into multiple lines of text at specified locations (see example below). My guess is that someone has already done something similar, and I was wondering if anyone had a "goby" for code that I could edit for my particular purposes. I tried to search the forums (keywords: create .txt file), but I’m not sure if there are keywords more appropriate. Any help would be greatly appreciated.

Data to be read from .xls file:
[A1] [B1] [C1] [D1] [E1] [F1] [G1]
400 0.003 0.7 0.200 0.3942 SB100 0.211

Output to .txt file:
"COMPUTE LT TP LCODE=1 UPLAND/LAG TIME TRANSITION METHOD
LENGTH=[A1] FT SLOPE=[B1] K=[C1]
Kn=[D1] CR=[E1]
COMPUTE NM HYD ID=1 HYD NO=[F1] DA=[G1] SQ MI
PER A=50 PER B=30 PER C=15 PER D=5
TP=0.0 MASSRAIN=-1"

Hello Bitter/Oso,

Welcome to the Forum!

Here is a macro that will copy the data from the active Excel worksheet into a text file. The file will be created if doesn't exist, and will overwrite the file if it does exist. You can change the starting row, the file name, and file path to what you are using. These are marked in the code in blue.

Macro to Write to Text File

Sub CreateTextFile()

  Dim FB As Integer       'File Buffer Number
  Dim FileName As String
  Dim FilePath As String
  Dim LastRow As Long
  Dim MyFile As String
  Dim R As Long
  Dim Rng As Range
  Dim StartRow As Long
  
    StartRow = 1
    With ActiveSheet.UsedRange
      LastRow = .Rows.Count + .Row - 1
    End With
    
      FB = FreeFile
      FileName = "XYZ.txt"
      FilePath = "C:\"
      MyFile = FilePath & "\" & FileName
      
        Open MyFile For Output As #FB
          For R = StartRow To LastRow
            Set Rng = Range(Cells(R, "A"), Cells(R, "G"))
            Print #FB, "COMPUTE LT TP LCODE=1 UPLAND/LAG TIME TRANSITION METHOD"
            Print #FB, "LENGTH=" & Rng(1, 1) & "FT SLOPE=" & Rng(1, 2) & "  K=" & Rng(1, 3)
            Print #FB, "Kn=" & Rng(1, 4) & "  CR=" & Rng(1, 5)
            Print #FB, "COMPUTE NM HYD ID=1 HYD NO=" & Rng(1, 6) & "  DA=" & Rng(1, 7) & " SQ MI"
            Print #FB, "PER A=50 PER B=30 PER C=15 PER D=5"
            Print #FB, "TP=0.0 MASSRAIN=-1"
            Print #FB,  'Add a blank line
          Next R
        Close #FB
               
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Leith,
The code works perfectly. Many thanks...you have no idea how much time this is going to save me.
Jesse

Hi, I find this code very useful cos I need to do something very similar. But the difference is, I have this excel sheet with random samples generated as follows.

A1 B1 C1 D1
0.01 0.13 0.21 0.001
0.11 0.09 0.25 0.002

and so on for 1000 rows.

My input file which i need to automate is as follows

'PORO' 0.01 ( this 0.01 has to be replaced by the values in the column A1)
'PERM' 0.12 ( This value has to be replaced by the values in column B1) and so on.. Each time the software inputs the file, the value should change automatically. I have the code to call the software. Can u help me pleasee..