none
Writing to a text file using a macro with VBA RRS feed

  • Question

  • Hello,

    I am using an excel macro and creating code in VBA to automate a text file with a specific format. All the data needed to create the text file is collected from the macro.

    Below is the format I would like to see for the output text file (which I am having issues  in creating) :

    EQUIPMENT_ID_DEF, 02, <eqID>, <“destination_group”>
    ; #### LABEL DEFINITION ####
    EQ_LABEL_DEF,02, <source_parm_name>
    UDB_LABEL, <description>
    STD_SUB_LABEL, “<description>, <LSB>, <MSB>, <sign>
    STD_ENCODING, <format>, <units>, <scale_factor>, <bits_num>, <decimals>
    END_EQ_LABEL_DEF

    Also, below is the VBA code I generated to get data from the macro and create/write into a text file. I still need to figure out how to write it in the specified format (output text file).

    Also, I am getting an error when I run the code. The error says: Run-time error '13': Type mismatch

    I am guessing it has to do with the  IF loop and trying to store string data into the <g class="gr_ gr_57 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="57" id="57">destgroup</g> parameter in the code…

    Thank you in advance!

    Sub ExcelToTxt()

    ‘Declaring variables
    Dim lCounter As Long
    Dim lLastRow As Long
    Dim destgroup As String
    Dim source_parmname as String

    Dim FName As Variant

    ‘Activate Sheet1
    Sheet1.Activate

    ‘Find the last row that contains data
    With Sheet1
    lLastRow = .Cells(.Rows.Count, “A”).End(xlDown).Row
    End With

    ‘Create txt file
    FName = Application.GetSaveAsFilename(“”, “txt file (*.txt), *.txt”)

    ‘Open FName For Output As #1’
    Open FName For Output As #1

    For lCounter = 2 To lLastRow

    ‘Read specific data from the worksheet
    With Sheet1
    destgroup = .Cells(lCounter, 2)
    source_parmname = .Cells(lCounter, 3)
    If destgroup = “trex_15hz” And “trex_10hz” And “trex_5hz” Then
    ‘Write selected data to text file
    Write #1, source_parmname

    End If
    End With

    ‘Continue looping until the last row
    Next lCounter

    ‘Close the text file
    Close #1

    End Sub


    Monday, October 9, 2017 3:20 AM

Answers

All replies