VBA code to add a some text lines from Excel to an existing text file

[tamatea2010]

Hello,

I need a vba code to copy some text lines extracted from an Excel sheet at a specific position inside an existing text (*.cfg) file.

Lines to be copied from active sheet, cell J2 to J20 :

[fltsim.x]
title=Carenado AT45 Air Tahiti (F-ORVA, "Te Maru Ata")
sim=AT45
texture=VTA_F-ORVA
atc_airline=AIR TAHITI
atc_id=F-ORVA
atc_flight_number=001
atc_heavy=0
atc_parking_types=GATE,RAMP,CARGO
atc_parking_code=VTA
atc_id_color=0000000000
ui_manufacturer=Avions de Transport Régional
ui_type=AT45
ui_variation=Air Tahiti (F-ORVA, "Te Maru Ata")
ui_typerole=Twin Engine TurboProp
ui_createdby=Carenado
version=1.1
visual_damage=1
description=Carenado\nATR 42-500. Repaint by Yann MAESTRATI.

Destination text file path :

"C:\Program Files\Lockheed Martin\Prepar3D v4\SimObjects\Airplanes\Carenado AT45\Aircraft.cfg"

Destination text file content :

//*********************************************************
// Carenado A42-500 FSX/P3D
// Copyright © Carenado 2019 - All Rights Reserved
//*********************************************************

[fltsim.0]
title=Carenado A42-500 HOUSE LIVERY
sim=AT45
ui_manufacturer="Carenado"
ui_type=AT42
ui_typerole="Twin Engine TurboProp"
ui_createdby="Carenado"
ui_variation="HOUSE LIVERY"
description="Carenado A42-500"
atc_heavy=0
atc_id_color=0000000000
visual_damage=1

[fltsim.1]
title=Carenado A42-500 D-BCJC
sim=AT45
texture=D-BCJC
atc_id=D-BCJC
ui_manufacturer="Carenado"
ui_type=AT42
ui_typerole="Twin Engine TurboProp"
ui_createdby="Carenado"
ui_variation="D-BCJC"
description="Carenado A42-500"
atc_heavy=0
atc_id_color=0000000000
visual_damage=1

[General]
atc_type=ATR
atc_model=AT45
editable=0

What is needed :

1- the code will read the text file and search for the last [fltsim.x] block

2- then it will read the "x" value, "1" in the exemple

3- then it will upadate the "x" amount of J2 cell value from "x" to "x+1", in the exemple J2 cell value will be changed from [fltsim.x] to [fltsim.2]

4- then it will copy all the lines (J2 to J20 cells values) and insert them inside the text file, after the last [fltsim.x] block, in the exemple after [fltsim.1] block

Expected text file content :

//*********************************************************
// Carenado A42-500 FSX/P3D
// Copyright © Carenado 2019 - All Rights Reserved
//*********************************************************

[fltsim.0]
title=Carenado A42-500 HOUSE LIVERY
sim=AT45
ui_manufacturer="Carenado"
ui_type=AT42
ui_typerole="Twin Engine TurboProp"
ui_createdby="Carenado"
ui_variation="HOUSE LIVERY"
description="Carenado A42-500"
atc_heavy=0
atc_id_color=0000000000
visual_damage=1

[fltsim.1]
title=Carenado A42-500 D-BCJC
sim=AT45
texture=D-BCJC
atc_id=D-BCJC
ui_manufacturer="Carenado"
ui_type=AT42
ui_typerole="Twin Engine TurboProp"
ui_createdby="Carenado"
ui_variation="D-BCJC"
description="Carenado A42-500"
atc_heavy=0
atc_id_color=0000000000
visual_damage=1

[fltsim.2]
title=Carenado AT45 Air Tahiti (F-ORVA, "Te Maru Ata")
sim=AT45
texture=VTA_F-ORVA
atc_airline=AIR TAHITI
atc_id=F-ORVA
atc_flight_number=001
atc_heavy=0
atc_parking_types=GATE,RAMP,CARGO
atc_parking_code=VTA
atc_id_color=0000000000
ui_manufacturer=Avions de Transport Régional
ui_type=AT45
ui_variation=Air Tahiti (F-ORVA, "Te Maru Ata")
ui_typerole=Twin Engine TurboProp
ui_createdby=Carenado
version=1.1
visual_damage=1
description=Carenado\nATR 42-500. Repaint by Yann MAESTRATI.

[General]
atc_type=ATR
atc_model=AT45
editable=0

 

[tamatea2010]

Please help guys, I do not how to solve this.

 

[DanteAmor]

Try this

Change Sheet1 to the name of your source sheet

Sub add_text_lines()
    Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet
    Dim wPath As String, wFile As String
    Dim f As Range, n As Variant, wRow As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")      'source sheet
    
    wPath = "C:\Program Files\Lockheed Martin\Prepar3D v4\SimObjects\Airplanes\Carenado AT45\"
    wFile = "Aircraft.cfg"
    If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
    Workbooks.OpenText Filename:=wPath & wFile, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
        TrailingMinusNumbers:=True
    
    Set wb = ActiveWorkbook
    Set sh2 = wb.Sheets(1)
    Set f = sh2.Range("A:A").Find("[fltsim", lookat:=xlPart, searchdirection:=xlPrevious)
    If Not f Is Nothing Then
        n = WorksheetFunction.Trim(Replace(Mid(f.Value, InStr(1, f.Value, ".") + 1, Len(f.Value)), "]", ""))
        n = n + 1
        wRow = f.End(xlDown)(3).Row
        sh2.Rows(wRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        sh1.Range("J2:J20").Copy
        sh2.Range("A" & wRow).Insert Shift:=xlDown
        sh2.Range("A" & wRow).Value = Replace(sh2.Range("A" & wRow).Value, ".x", "." & n)
    End If
    wb.SaveAs Filename:=wPath & wFile, FileFormat:=xlTextPrinter, CreateBackup:=False
    wb.Close False
    MsgBox "inserted lines"
End Sub

 

[DSCfromCFA]

G'day tamatea,

By sheer coincidence i was reading this article just a few hours ago,

http://www.cpearson.com/Excel/ExportFixedWidth.aspx

and it has a companion article:

http://www.cpearson.com/Excel/ImpText.aspx

Note 1: these articles are somewhat dated, but the associated explanations of the code I find very helpful.

URL of the Topic Index

http://www.cpearson.com/Excel/Topic.aspx

Note 2: I am in no way associated with this site, but learnt of it from a post in this forum.

Cheers

shane

 

[tamatea2010]

DanteAmor, thanks alot it perfectly works!

 

[tamatea2010]

DCSfromCFA, thanks for the links, very interresting.

 

[DanteAmor]

DanteAmor, thanks alot it perfectly works!

I'm glad to help you. I appreciate your kind comments.