View original (French)

Set up a macro that will generate a txt file

Solved
AHI_250 Posted messages 25 Status Member -  
KARROUMAYAGHALI Posted messages 10 Status Member -
Hello everyone,

I am really new to programming and I am looking for a procedure, a document, or a detailed explanation please that will allow me to set up a macro in an Excel file to generate a flat file (txt) to avoid data manipulation. I will need a header (with information delimited by |) in the file and then rows with multiple columns delimited by "|".

Thank you in advance.

Best regards,

27 answers

  • 1
  • 2
  1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    To test...
    I haven't tried it and wrote it here, so there may be some errors...
    TO ADAPT: see comments in the macro 

    Sub TestCreationFichierTxt() Dim MesDonnees() Dim Chemin As String, Ligne As String Dim num As Integer Dim i As Long, j As Long, DernLigne As Long num = FreeFile With Sheets("Feuil1") ' TO ADAPT the name of the sheet containing the data 'TO ADAPT in case column A is not the "longest" DernLigne = .Range("A" & Rows.Count).End(xlUp).Row 'TO ADAPT here only processes columns A to E MesDonnees = .Range("A1:E" & DernLigne).Value End With 'TO ADAPT: full path for storing the txt file Chemin = "C:\Users\MoiMeme\Desktop" 'TO ADAPT the name of the txt file to create Open Chemin & "\MonFichierTexte.txt" For Output As #num 'Loop through the list of words For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1) For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2) If j = LBound(MesDonnees, 2) Then Ligne = MesDonnees(i, j) & " |" Else Ligne = Ligne & MesDonnees(i, j) & " |" End If Next j 'Writes to the text file line by line Print #1, Ligne Ligne = "" Next i 'Close Close #num End Sub


    --
    Best regards,
    Franck
    1
    1. KARROUMAYAGHALI Posted messages 10 Status Member
       

      Hello pijaku,

      I am really new to programming, and I am looking for a procedure that will allow me to set up a macro using a button in an Excel file to generate a text file (txt) (knowing that the file must be generated from the 3rd sheet of my Excel file.

      The 3rd sheet is named CNSS and contains

      - a header over 4 lines

      - the values to be recorded in the file start from the 5th line, 2nd column (B5) containing:

             * matricule1: 8 characters;  clé1: 2 characters;  codeExp: 4 digits; quarter: 1 character; Year: 4 characters; Page number: 3 characters; Line number: 2 characters; matricule2: 8 characters; clé2: 2 characters; Identity: 60 characters; card number: 8 characters; salary: 10 characters; zone: 10 characters

      There are a maximum of 100 lines

      (The recording without spaces or characters)

      0024275864000012023001010000000000aaaaaaaaaaaaaaaaaaaaaaaaaaa                                              0000000000090000000000000000

      File name to generate: matricule1clé1codeExpTrimestreAnnée

      Directory: of your choice

      Name

      Achraf

      Thank you in advance.

      Best regards,

      0
  2. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    Would a .csv file not be suitable?

    Best regards,
    Franck
    0
  3. AHI_250 Posted messages 25 Status Member
     
    Hello Franck,

    In fact, the server that is dedicated to processing the file is waiting for a .txt file. Thank you if you could let me know the path, please.

    Best regards,
    AHI
    0
  4. AHI_250 Posted messages 25 Status Member
     
    Thank you very much Franck, I adapted the .txt file and created it well. My current issue is that the number of columns in the header is different from the number of columns in the other lines: the header has 4 columns and the other lines have 6 columns, and the columns are defined with a specific length, for example, the date is 14 and the employee number is 5. Please let me know how to add this to the macro code.

    Thank you very much.

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      For the headers, write one for columns E and F...
      And don't forget to adjust the macro on this line: 
      MyData = .Range("A1:E" & LastRow).Value
      which becomes: 
      MyData = .Range("A1:F" & LastRow).Value
      Could you give me the number of characters per column, like this: col A: 5 chars, etc...

      However, keep in mind that depending on the graphical resolution of the fonts, and the size of the letters themselves, your txt file will never look the same as an Excel workbook with nicely "squared" columns...

      Look here:
      14 m
      mmmmmmmmmmmmmm
      14 i
      iiiiiiiiiiiiii

      Do you understand?
      0
  6. AHI_250 Posted messages 25 Status Member
     
    For the header:

    col A:3 because
    Col B:8 but if I enter 4 because for example in the Excel file, the rest should be filled with 00 in the .txt file
    col C:12
    col D:14

    for the other rows
    col A:5 because
    Col B:11
    col C:3
    col D:12
    col C:20
    col E:20
    col F:1

    for MesDonnees = .Range("A1:E" & DernLigne).Value I had already adapted that

    MesDonnees = .Range("A1:G" & DernLigne).Value

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      I don't quite understand your request...
      0
  7. AHI_250 Posted messages 25 Status Member
     
    In fact, the output file format, that is, the .txt has two parts:

    -a header
    -and records

    and therefore

    For the header:

    col A:3 because
    Col B:8
    col C:12
    col D:14

    for the other lines
    col A:5 because
    Col B:11
    col C:3
    col D:12
    col C:20
    col E:20
    col F:1

    I don’t know if I’m making myself clear.
    for example:

    1 |00000030|000204583100|05052014093526
    01081|08705152436|674|000007928500|AHI |MANGA
    01031|09977967643|952|000004270000|ZOOZOUU |JOJOUA

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Okay, but what do you want? What is your question?
      Don't forget that I don't have your data in front of me...
      0
  8. AHI_250 Posted messages 25 Status Member
     
    I believe I will share my concerns with you step by step, please.

    For the header with the code you gave me, when I generate my .txt file, I have two "|" that continue because in the code it was defined for 6 columns. How can I fix this?

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Yes, let's do it that way. 

      Sub TestCreationFichierTxt() Dim MesDonnees() Dim Chemin As String, Ligne As String Dim num As Integer Dim i As Long, j As Long, DernLigne As Long num = FreeFile With Sheets("Sheet1") ' ADAPT THE name of the sheet containing the data 'ADAPT in case column A is not the longest' DernLigne = .Range("A" & Rows.Count).End(xlUp).Row 'ADAPT here only processes columns A to E MesDonnees = .Range("A1:E" & DernLigne).Value End With 'ADAPT: full path for storing the txt file Chemin = "C:\Users\Myself\Desktop" 'ADAPT the name of the txt file to create Open Chemin & "\MyTextFile.txt" For Output As #num 'Loop through the list of words For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1) For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2) If j = LBound(MesDonnees, 2) Then Ligne = MesDonnees(i, j) & " |" Else Ligne = Ligne & MesDonnees(i, j) & " |" End If Next j 'Here we deal with the header row issue... If i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 4) 'Writes to the text file line by line Print #1, Ligne Ligne = "" Next i 'Closing Close #num End Sub
      0
  9. AHI_250 Posted messages 25 Status Member
     
    Please, I have an error message "compilation error expected end of statement"

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      erreur de copier-coller.
      Remplacer : 
      If i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 4)
      par : 
      i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 4)
      0
  10. AHI_250 Posted messages 25 Status Member
     
    Here's what I have in the generated file with the new code

    1 |30 |204583100 |05052014093526 |

    I don't want to have the last two "|"

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      With this code? 

      Sub TestCreationFichierTxt() Dim MesDonnees() Dim Chemin As String, Ligne As String Dim num As Integer Dim i As Long, j As Long, DernLigne As Long num = FreeFile With Sheets("Feuil1") ' TO ADAPT the name of the sheet containing the data 'TO ADAPT in case column A is not the "longest" DernLigne = .Range("A" & Rows.Count).End(xlUp).Row 'TO ADAPT here only processes columns from A to E MesDonnees = .Range("A1:E" & DernLigne).Value End With 'TO ADAPT: full path for storing the txt file Chemin = "C:\Users\MoiMeme\Desktop" 'TO ADAPT the name of the txt file to create Open Chemin & "\MonFichierTexte.txt" For Output As #num 'Loop through the list of words For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1) For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2) If j = LBound(MesDonnees, 2) Then Ligne = MesDonnees(i, j) & " |" Else Ligne = Ligne & MesDonnees(i, j) & " |" End If Next j 'Here we treat the issue of the header line... If i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 4) 'Writes in the text file line by line Print #1, Ligne Ligne = "" Next i 'Closure Close #num End Sub


      I get a header line like this:
      1 |30 |204583100 |05052014093526 |

      To get:
      1 |30 |204583100 |05052014093526

      you need to replace: 
       If i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 4)
      with: 
       If i = LBound(MesDonnees, 1) Then Ligne = Left(Ligne, Len(Ligne) - 6)
      0
  11. AHI_250 Posted messages 25 Status Member
     
    ok this issue is resolved thank you very much regarding the same header

    I have a problem with the positions actually
    1 |30 |204583100 |05052014093526

    the 2nd column that is, where it says 30, is on 8 positions so I want to have in the .txt file

    1 |00000030 |204583100 |05052014093526 when I put 30 in the Excel file or if I put 123 in Excel I want to have

    in the .txt 1 |00000123 |204583100 |05052014093526

    hoping that I make myself understood

    Sincerely,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
        
      Sub TestCreationFichierTxt()
    Dim MesDonnees()
    Dim Chemin As String, Ligne As String, ColB As String, ColBInit As String
    Dim num As Integer
    Dim i As Long, j As Long, DernLigne As Long
    num = FreeFile
    With Sheets("Feuil1")
        ' A ADAPTER le nom de la feuille contenant les données
        'A ADAPTER au cas ou la colonne A ne serait pas la plus "longue"
        DernLigne = .Range("A" & Rows.Count).End(xlUp).Row
        'A ADAPTER ici ne traite que les colonnes de A à E
        MesDonnees = .Range("A1:E" & DernLigne).Value
    End With
    'A ADAPTER : chemin d'accès complet pour le stockage du fichier txt
    Chemin = "C:\Users\MoiMeme\Desktop"
    'A ADAPTER le nom du fichier txt à créer
    Open Chemin & "\MonFichierTexte.txt" For Output As #num
    'Boucle sur la liste des mots
    For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1)
        For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2)
            If j = LBound(MesDonnees, 2) Then
                Ligne = MesDonnees(i, j) & " |"
            Else
                Ligne = Ligne & MesDonnees(i, j) & " |"
            End If
        Next j
        'Ici on traite le souci de la ligne d'entêtes...
        If i = LBound(MesDonnees, 1) Then
            Ligne = Left(Ligne, Len(Ligne) - 4)
            ColBInit = Split(Ligne, " |")(1)
            ColB = ColBInit
            Do While Len(ColB) < 8
                ColB = "0" & ColB
            Loop
            Ligne = Replace(Ligne, ColBInit, ColB)
        End If
        'Ecrit dans le fichier texte ligne par ligne
        Print #num, Ligne
        Ligne = ""
    Next i
    'Fermeture
    Close #num
End Sub
      0
  12. AHI_250 Posted messages 25 Status Member
     
    It's okay, thank you.

    always header

    1 |00000030|000000012300|05052014093526

    the 1st column, that is to say where it is mi 1, is on 3 positions, I want to have in the .txt
    three positions for this column
    for example:

    1 |00000030|000000012300|05052014093526

    and for the 3rd column it is on 12 positions when I put 123 in Excel, I want to have in the .txt file

    1 |00000030|000000012300|05052014093526

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Did you understand how I transformed 30 into 00000030?
      this part of the code: 
       ColBInit = Split(Line, " |")(1) ColB = ColBInit Do While Len(ColB) < 8 ColB = "0" & ColB Loop Line = Replace(Line, ColBInit, ColB)

      If you understand it, you can do the same for the other columns...
      0
  13. AHI_250 Posted messages 25 Status Member
     
    Sorry for the delay in my response. I had actually made this modification before sending you my last request. Here it is:

    ColAInit = Split(Line, " | ")(1)
    ColA = ColAInit
    Do While Len(ColA) < 3
    ColA = " " & ColA
    Loop
    Line = Replace(Line, ColAInit, ColA)

    It didn't give me any response. Can you please take a look at my code?

    Best regards,
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      The Split function (see the online help for this function)
      Suppose Line = "Part1 |Part2 |Part 3 |Pat4" 
      Split(Line, " |")(0)
      => returns Part 1 
      Split(Line, " |")(1)
      => returns Part2 
      Split(Line, " |")(2)
      => returns Part 3 
      Split(Line, " |")(3)
      => returns Pat4
      So for your column A use 
      Split(Line, " |")(0)

      Be careful with Replace as well. 
      Line = Replace(Line, ColAInit, ColA)
      If ColAInit = 1, all the digit 1s in Line will be replaced by " 1".
      Since it is the left part of Line, we just need to retrieve the right part after the first " |", so use instead: 
      Line = ColA & Right(Line, Len(Line) -3)

      It's just "simple" character manipulation...
      0
  14. AHI_250 Posted messages 25 Status Member
     
    Sub TestCreationFichierTxt()
    Dim MesDonnees()
    Dim Chemin As String, Ligne As String, colA As String, colAInit As String, ColB As String, ColBInit As String
    Dim num As Integer
    Dim i As Long, j As Long, DernLigne As Long
    num = FreeFile
    With Sheets("Feuil1") ' ADAPT the name of the sheet containing the data
    'ADAPT in case column A is not the "longest"
    DernLigne = .Range("A" & Rows.Count).End(xlUp).Row
    'ADAPT here only processes columns A to E
    MesDonnees = .Range("A1:G" & DernLigne).Value
    End With
    'ADAPT: full path for storing the txt file
    Chemin = "C:\Users\moi-meme\Desktop"
    'ADAPT the name of the txt file to create
    Open Chemin & "\MonFichierTexte.txt" For Output As #num
    'Loop through the list of words
    For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1)
    For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2)
    If j = LBound(MesDonnees, 2) Then
    Ligne = MesDonnees(i, j) & "|"
    Else
    Ligne = Ligne & MesDonnees(i, j) & "|"
    End If
    Next j
    'Here we handle the header row issue...
    If i = LBound(MesDonnees, 1) Then
    Ligne = Left(Ligne, Len(Ligne) - 7)
    colAInit = Split(Ligne, "|")(1)
    colA = colAInit
    Do While Len(colA) < 3
    ColB = " " & colA
    Loop
    Ligne = Replace(Ligne, colAInit, colA)
    ColBInit = Split(Ligne, "|")(1)
    ColB = ColBInit
    Do While Len(ColB) < 8
    ColB = "0" & ColB
    Loop
    Ligne = Replace(Ligne, ColBInit, ColB)
    End If
    'Writes to the text file line by line
    Print #1, Ligne
    Ligne = ""
    Next i
    'Closing
    Close #num
    End Sub

    Voilà tout mon code.
    0
  15. AHI_250 Posted messages 25 Status Member
     
    Good evening,

    I had some issues with Excel and had to restart my machine because it kept crashing. I modified my code, but here’s what I find: it adds two extra columns.

    1|00000123|00000123|12062010000123|00000123|1206201

    code:

    'Here we address the header line issue...
    If i = LBound(MyData, 1) Then
    Line = Left(Line, Len(Line) - 7)
    ColBInit = Split(Line, "|")(1)
    ColB = ColBInit
    Do While Len(ColB) < 8
    ColB = "0" & ColB
    Loop
    Line = Replace(Line, ColBInit, ColB)
    End If

    If i = LBound(MyData, 1) Then
    ColAInit = Split(Line, " |")(0)
    ColA = ColAInit
    Do While Len(ColA) < 3
    Loop
    Line = ColA & Right(Line, Len(Line) - 3)
    End If

    Best regards,
    0
  16. AHI_250 Posted messages 25 Status Member
     
    I know I'm repetitive, but I just wanted to clarify that the first column is on 3 positions, and when I enter 1 in Excel, I want to have in my .txt:

    1 |Part2|Part3|Part4

    Best regards,
    0
  17. AHI_250 Posted messages 25 Status Member
     
    Hello FRANCK,

    Please could you help me finish my macro? I really need your response.

    Best regards,
    0
  18. AHI_250 Posted messages 25 Status Member
     
    Hello,

    I was able to resolve the issue with column A, but I would like assistance with column C (which has 12 positions) in order to create a .txt file with content formatted as follows:

    If I input X in Excel, I would like to get 000000000X00 in the .txt file; if I input 450, I would like to get 000000045000. The code I currently have allows me to retain the value from Excel, and the other positions are filled with "0". However, I want two "0"s after the value. I’m leaving you my code; please help me fix this issue, thank you.

    Sub TestCreationFichierTxt()
    Dim MesDonnees()
    Dim Chemin As String, Ligne As String, ColB As String, ColBInit As String, ColC As String, ColCInit As String
    Dim num As Integer
    Dim i As Long, j As Long, DernLigne As Long
    num = FreeFile
    With Sheets("Feuil1") ' TO BE ADAPTED: the name of the sheet containing the data
    'TO BE ADAPTED in case column A is not the "longest"
    DernLigne = .Range("A" & Rows.Count).End(xlUp).Row
    'TO BE ADAPTED: here only processes columns A to E
    MesDonnees = .Range("A1:E" & DernLigne).Value
    End With
    'TO BE ADAPTED: full path for storing the txt file
    Chemin = "C:\Users\me\Desktop"
    'TO BE ADAPTED: the name of the txt file to create
    Open Chemin & "\MyTextFile.txt" For Output As #num
    'Loop through the list of words
    For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1)
    For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2)
    If j = LBound(MesDonnees, 2) Then
    Ligne = MesDonnees(i, j) & " |"
    Else
    Ligne = Ligne & MesDonnees(i, j) & "|"
    End If
    Next j
    'Here we handle the header row issue...
    If i = LBound(MesDonnees, 1) Then
    Ligne = Left(Ligne, Len(Ligne) - 7)
    ColBInit = Split(Ligne, "|")(1)
    ColB = ColBInit
    Do While Len(ColB) < 8
    ColB = "0" & ColB
    Loop
    Ligne = Replace(Ligne, ColBInit, ColB)
    End If

    If i = LBound(MesDonnees, 1) Then
    ColCInit = Split(Ligne, "|")(2)
    ColC = ColCInit
    Do While Len(ColC) < 12
    ColC = "0" & ColC & "0"
    Loop
    Ligne = Replace(Ligne, ColCInit, ColC)
    End If
    'Write to the text file line by line
    Print #1, Ligne
    Ligne = ""
    Next i
    'Closing
    Close #num
    End Sub

    This code gives me: A |B|000045000000|

    I hope I was clear

    I remain available for any further information

    Best regards.
    0
  19. AHI_250 Posted messages 25 Status Member
     
    Good evening everyone,

    is there really no one to help me please?

    Best regards,
    0
    1. Polux31 Posted messages 7219 Status Member 1 204
       
      Hello,

      You have all the information you need to do what you want to do (Hi Frank, and hats off to your patience).
      I don't really feel like you've read and applied all the recommendations given by Frank. You're just copy-pasting the code he gives you without really trying to understand it.

      What you're asking for is nothing more than string manipulation.
      0
  20. AHI_250 Posted messages 25 Status Member
     
    Good evening,

    In fact, I understand what you are saying and I would like to thank FRANCK, but I had specified at the beginning that I am not really knowledgeable in programming. I understood what FRANCK did, which is why I was able to do the same for column C.

    If i = LBound(MesDonnees, 1) Then
    ColCInit = Split(Ligne, "|")(2)
    ColC = ColCInit
    Do While Len(ColC) < 12
    ColC = "0" & ColC & "0"
    Loop
    Ligne = Replace(Ligne, ColCInit, ColC)
    End If

    But the problem is that I can't position the values where they should go. Thank you anyway for responding, and I ask you to help me finish please.

    Best regards,
    0
  21. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    At this stage, we will separate the two procedures:
    1- the Sub that writes the txt file
    2- A function to process the header line. 

    Sub Main_CreationFichierTxt() Dim MesDonnees() Dim Chemin As String, Ligne As String Dim num As Integer Dim i As Long, j As Long, DernLigne As Long num = FreeFile With Sheets("Feuil1") ' ADAPT THE name of the sheet containing the data 'ADAPT in case column A is not the "longest" DernLigne = .Range("A" & Rows.Count).End(xlUp).Row 'ADAPT here only processes columns A to E MesDonnees = .Range("A1:E" & DernLigne).Value End With 'ADAPT: full path for storing the txt file Chemin = "C:\Users\MoiMeme\Desktop" 'ADAPT the name of the txt file to create Open Chemin & "\MonFichierTexte.txt" For Output As #num 'Loop through the list of words For i = LBound(MesDonnees, 1) To UBound(MesDonnees, 1) For j = LBound(MesDonnees, 2) To UBound(MesDonnees, 2) If j = LBound(MesDonnees, 2) Then Ligne = MesDonnees(i, j) & " |" Else Ligne = Ligne & MesDonnees(i, j) & " |" End If Next j 'Here we call the header processing function... If i = LBound(MesDonnees, 1) Then Format_Entete Ligne 'Writes to the text file line by line Print #1, Ligne Ligne = "" Next i 'Closure Close #num End Sub Function Format_Entete(Entete As String) As String Dim ColB As String, ColBInit As String Dim ColC As String, ColCInit As String Entete = Left(Entete, Len(Entete) - 4) 'Place here the processing of column A 'processing column B ColBInit = Split(Entete, " |")(1) ColB = ColBInit Do While Len(ColB) < 8 ColB = "0" & ColB Loop Entete = Replace(Entete, ColBInit, ColB) 'processing column C ColCInit = Split(Entete, "|")(2) & "00" 'here we add the two zeros after ColC = ColCInit Do While Len(ColC) < 12 ColC = "0" & ColC Loop Entete = Replace(Entete, ColCInit, ColC) 'Place here the processing of column D Format_Entete = Entete End Function


    In this code, there may be some small errors as I have not tested it.
    So let's see

    Regards,
    Franck
    0
  • 1
  • 2