Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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).

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

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

    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 #f1 , 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 #f2 , strLine
            Loop
        Close f1
        Close f2
        
        Kill strSourceFile
        
        Name strTempFile As strSourceFile
        
        MsgBox "Completed!", vbExclamation
        
    End Sub
    Hope this helps!

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Domenic thanks aalot for your kind help for a newbie in VBA like me

    Your code is working and perfectly reply to my needs.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •