I need to replace strings of a text file with cell values extracted from an excel file

tamatea2010

New Member
Joined
Apr 22, 2019
Messages
6
The text path is:
d:\Users\Yann\Desktop\QW787_Fuel.cfg


The text content is:
[Config]
Units=1
[fuel.0]
LeftMain=36983
RightMain=36983
Center=22818

The values to be replaced are the numbers after:
LeftMain=
RightMain=
Center=

An example of the new values that will be extracted from Excel file are:
Cell D43 value = 5000
Cell D45 value = 5000
Cell D44 value = 600

Expected text file after replacement:
[Config]
Units=1
[fuel.0]
LeftMain=5000
RightMain=5000
Center=600

The values to be replaced are variable (numbers can vary from 0 to 99999).
I do not know much about vba and need your help guys to have a vba code that will be launched from Excel and replace the values in the text file after I change them in excel and launched the code, without seing the text file openning (like a bckground process).
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
The following macro assumes that the sheet containing the replacement values is the active sheet...

Code:
Option Explicit

Sub ReplaceTextValues()


    Dim strPath As String
    Dim strSourceFile As String
    Dim strTempFile As String
    Dim strLine As String
    Dim f1 As Integer
    Dim f2 As Integer
    
    strPath = "D:\Users\Yann\Desktop\"
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MsgBox "Path not found!", vbExclamation
        Exit Sub
    End If
    
    strSourceFile = strPath & "QW787_Fuel.cfg"
    
    If Len(Dir(strSourceFile, vbNormal)) = 0 Then
        MsgBox "File not found!", vbExclamation
        Exit Sub
    End If
    
    strTempFile = Environ("temp") & "\" & "temp.txt"
    
    f1 = FreeFile()
    Open strSourceFile For Input As f1
    f2 = FreeFile()
    Open strTempFile For Output As f2
        Do Until EOF(f1)
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f1]#f1[/URL] , strLine
            If UCase(Left(strLine, 8)) = "LEFTMAIN" Then
                strLine = "LeftMain=" & Range("D43").Value
            ElseIf UCase(Left(strLine, 9)) = "RIGHTMAIN" Then
                strLine = "RightMain=" & Range("D45").Value
            ElseIf UCase(Left(strLine, 6)) = "CENTER" Then
                strLine = "Center=" & Range("D44").Value
            End If
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f2]#f2[/URL] , strLine
        Loop
    Close f1
    Close f2
    
    Kill strSourceFile
    
    Name strTempFile As strSourceFile
    
    MsgBox "Completed!", vbExclamation
    
End Sub

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top