locked
Writing to a text file using an excel 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 <g class="gr_ gr_17 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" data-gr-id="17" id="17">excel  macro</g>.

    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 destgroup 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



    • Edited by jesus_10 Monday, October 9, 2017 3:18 AM
    Monday, October 9, 2017 3:13 AM

All replies

  • Try changing your If to this:

    If destgroup = "trex_15hz" Or destgroup = "trex_10hz" Or destgroup = "trex_5hz" Then

    As far as the formatting of your output - whatever is in column C should appear in your text file as text. So control your text in column C to get the correct output.

    Tuesday, October 10, 2017 2:09 PM