Create a txt file from the data in the excel sheet

**Nyepalo** · 02-07-2015, 12:09 PM

Hello,
I need your help to export to a text file the information in the sheet "Detail".
- The Head of the text file is in my script. Only the date in that header changes, it is the day wich the text file is created.
- Records detail (0602): There are many records as rows in the Sheet.
- A total record (0802): This is the last of the table and it is described only once in the text file.

The empty spaces are part of the txt file and space should match the field length considering a space is a character.
As below

The reserve1 field = String (8, Chr (160).

Sorry for my English.

Regards.

**Leith Ross** · 02-07-2015, 10:26 PM

Hello Nyepalo,

Welcome to the Forum!

This macro will output the worksheet data to a text file. Pass the file path and name when you call the macro.

Example:

Call ExportDonnées(ThisWorkbook.Path & "\" & "Texte_AFB160.txt")

Macro Code

Sub ExportDonnées(ByVal nomFich As String)

  ' Format largeur fixe
  
    Dim Data    As Variant
    Dim r       As Long
    Dim Rng     As Range
    Dim Text    As String
   
        Set Rng = Sheet1.Range("A1").CurrentRegion
        
        Data = Rng.Value
        
        Open nomFich For Output As #1
            For r = 1 To UBound(Data, 1)
                Text = String(160, Chr(160))
                
                If Not IsEmpty(Data(r, 1)) Then Mid(Text, 1, 4) = Data(r, 1)
                If Not IsEmpty(Data(r, 2)) Then Mid(Text, 13, 6) = Data(r, 2)
                If Not IsEmpty(Data(r, 3)) Then Mid(Text, 19, 12) = Data(r, 3)
                If Not IsEmpty(Data(r, 4)) Then Mid(Text, 31, 24) = Data(r, 4)
                If Not IsEmpty(Data(r, 5)) Then Mid(Text, 55, 24) = Data(r, 5)
                If Not IsEmpty(Data(r, 6)) Then Mid(Text, 87, 5) = Data(r, 6)
                If Not IsEmpty(Data(r, 7)) Then Mid(Text, 92, 11) = Data(r, 7)
                If Not IsEmpty(Data(r, 8)) Then Mid(Text, 103, 16) = Format(Data(r, 8), "0000000000000000")
                If Not IsEmpty(Data(r, 9)) Then Mid(Text, 119, 31) = Data(r, 9)
                If Not IsEmpty(Data(r, 10)) Then Mid(Text, 150, 5) = Data(r, 10)
                
                Print #1, Text
            Next r
        Close #1
        
End Sub

**Nyepalo** · 02-09-2015, 08:50 AM

Hi Ross,

Thank you very much for your speed, it's perfect, exactly what I was looking for, thank you again

I want also to integrated the header line in the file.txt. The Header line is one record and contains data that does not change except for the date of file creation witch is in the jjmma. I tried to fit it in your script, I didnt succeeded.
See my code

Print #fichier, "0302" & String(8, Chr(160)) & "012345" & String(7, Chr(160)) & "JJMMA" & "SOCIETE TANTAMPION SARL " & String(14, Chr(160)) & "PAIEMEN" & String(25, Chr(160)) & "12345" & "01234567890" & "TANTAMPION SARL " & String(31, Chr(160)) & "10001" & String(6, Chr(160))

Regards.

**Leith Ross** · 02-09-2015, 12:50 PM

Hello Nyepalo,

Your change was close to correct. Here is the macro with your header code added. The header appears to be longer than 160 characters. Is it supposed to wider than the other lines?

Sub ExportDonnées(ByVal nomFich As String)

  ' Format largeur fixe

    Dim Data    As Variant
    Dim r      As Long
    Dim Rng    As Range
    Dim Text    As String

      
        Set Rng = Feuil1.Range("A1").CurrentRegion
        Data = Rng.Value

        Open nomFich For Output As #1
            Print #1, "0302" & String(8, Chr(160)) & "012345" & String(7, Chr(160)) & "JJMMA" & "SOCIETE TANTAMPION SARL " & String(14, Chr(160)) _
                        & "PAIEMEN" & String(25, Chr(160)) & "12345" & "01234567890" & "TANTAMPION SARL " & String(31, Chr(160)) & "10001" & String(6, Chr(160))
                            
            For r = 1 To UBound(Data, 1)
                Text = String(160, Chr(160))

                If Not IsEmpty(Data(r, 1)) Then Mid(Text, 1, 4) = Data(r, 1)
                If Not IsEmpty(Data(r, 2)) Then Mid(Text, 13, 6) = Data(r, 2)
                If Not IsEmpty(Data(r, 3)) Then Mid(Text, 19, 12) = Data(r, 3)
                If Not IsEmpty(Data(r, 4)) Then Mid(Text, 31, 24) = Data(r, 4)
                If Not IsEmpty(Data(r, 5)) Then Mid(Text, 55, 24) = Data(r, 5)
                If Not IsEmpty(Data(r, 6)) Then Mid(Text, 87, 5) = Data(r, 6)
                If Not IsEmpty(Data(r, 7)) Then Mid(Text, 92, 11) = Data(r, 7)
                If Not IsEmpty(Data(r, 8)) Then Mid(Text, 103, 16) = Format(Data(r, 8), "0000000000000000")
                If Not IsEmpty(Data(r, 9)) Then Mid(Text, 119, 31) = Data(r, 9)
                If Not IsEmpty(Data(r, 10)) Then Mid(Text, 150, 5) = Data(r, 10)

                Print #1, Text
            Next r
        Close #1

End Sub

**Nyepalo** · 02-09-2015, 02:57 PM

Hi Ross,
The header is not more than 160 caracters.

The Script should be :

Print #1, "0302" & String(8, Chr(160)) & "012345" & String(7, Chr(160)) & "09025" & "SOCIETE TANTAMPION SARL " _
                        & "PAIEMEN" & String(25, Chr(160)) & "12345" & "01234567890" & "TANTAMPION SARL " & String(31, Chr(160)) & "10001" & String(6, Chr(160))

I must add the current date in the header like ddmma :

Day(datejour) & Month(datejour) & Year(datejour) - 2010

But is not corect because the result is 925 it must be 09025.

Sub ExportDonnées(ByVal nomFich As String)

  ' Format largeur fixe

    Dim Data    As Variant
    Dim r      As Long
    Dim Rng    As Range
    Dim Text    As String
    Dim mois As String
    Dim datejour As Date
      
        Set Rng = Feuil1.Range("A1").CurrentRegion
        Data = Rng.Value
        datejour = Now
        
        Open nomFich For Output As #1
        Print #1, "0302" & String(8, Chr(160)) & "012071" & String(7, Chr(160)) & Day(datejour) & Month(datejour) & Year(datejour) - 2010 & "SOCIETE TANTAMPION SARL " _
                        & "PAIEMEN" & String(25, Chr(160)) & "06800" & "01234567890" & "TANTAMPION SARL " & String(31, Chr(160)) & "10001" & String(6, Chr(160))
            
                        
            For r = 2 To UBound(Data, 1)
                Text = String(160, Chr(160))

                If Not IsEmpty(Data(r, 1)) Then Mid(Text, 1, 4) = Data(r, 1)
                If Not IsEmpty(Data(r, 2)) Then Mid(Text, 13, 6) = Data(r, 2)
                If Not IsEmpty(Data(r, 3)) Then Mid(Text, 19, 12) = Data(r, 3)
                If Not IsEmpty(Data(r, 4)) Then Mid(Text, 31, 24) = Data(r, 4)
                If Not IsEmpty(Data(r, 5)) Then Mid(Text, 55, 24) = Data(r, 5)
                If Not IsEmpty(Data(r, 6)) Then Mid(Text, 87, 5) = Data(r, 6)
                If Not IsEmpty(Data(r, 7)) Then Mid(Text, 92, 11) = Data(r, 7)
                If Not IsEmpty(Data(r, 8)) Then Mid(Text, 103, 16) = Format(Data(r, 8), "0000000000000000")
                If Not IsEmpty(Data(r, 9)) Then Mid(Text, 119, 31) = Data(r, 9)
                If Not IsEmpty(Data(r, 10)) Then Mid(Text, 150, 5) = Data(r, 10)

                Print #1, Text
            Next r
        Close #1

End Sub

Thanks.

**Leith Ross** · 02-09-2015, 03:05 PM

Hello Nyepalo,

Is the jjmma today's date? That is the date the macro is run? Or does this date come from someplace else in the workbook?

**Nyepalo** · 02-09-2015, 03:21 PM

That is the date the macro is run ? Yes

**Leith Ross** · 02-09-2015, 03:41 PM

Hello Nyepalo,

This should work...

Sub ExportDonnées(ByVal nomFich As String)

  ' Format largeur fixe

    Dim Data    As Variant
    Dim r       As Long
    Dim Rng     As Range
    Dim Text    As String

      
        Set Rng = Feuil1.Range("A1").CurrentRegion
        Data = Rng.Value

        Open nomFich For Output As #1
            Print #1, "0302" & String(8, Chr(160)) & "012345" & String(7, Chr(160)) & Format(Date, "ddmm") & Right(Year(Date), 1) _
                    & "SOCIETE TANTAMPION SARL " & String(14, Chr(160)) & "PAIEMEN" & String(25, Chr(160)) & "12345" & "01234567890" _
                    & "TANTAMPION SARL " & String(31, Chr(160)) & "10001" & String(6, Chr(160))
                            
            For r = 1 To UBound(Data, 1)
                Text = String(160, Chr(160))

                If Not IsEmpty(Data(r, 1)) Then Mid(Text, 1, 4) = Data(r, 1)
                If Not IsEmpty(Data(r, 2)) Then Mid(Text, 13, 6) = Data(r, 2)
                If Not IsEmpty(Data(r, 3)) Then Mid(Text, 19, 12) = Data(r, 3)
                If Not IsEmpty(Data(r, 4)) Then Mid(Text, 31, 24) = Data(r, 4)
                If Not IsEmpty(Data(r, 5)) Then Mid(Text, 55, 24) = Data(r, 5)
                If Not IsEmpty(Data(r, 6)) Then Mid(Text, 87, 5) = Data(r, 6)
                If Not IsEmpty(Data(r, 7)) Then Mid(Text, 92, 11) = Data(r, 7)
                If Not IsEmpty(Data(r, 8)) Then Mid(Text, 103, 16) = Format(Data(r, 8), "0000000000000000")
                If Not IsEmpty(Data(r, 9)) Then Mid(Text, 119, 31) = Data(r, 9)
                If Not IsEmpty(Data(r, 10)) Then Mid(Text, 150, 5) = Data(r, 10)

                Print #1, Text
            Next r
        Close #1

End Sub

**Nyepalo** · 02-10-2015, 01:24 PM

Hello Ross,

You're fantastic, everything is in order as I wanted. Thank you very much

God bless you.

**Leith Ross** · 02-10-2015, 01:33 PM

Hello Nyepalo,

You're welcome.

**Nyepalo** · 03-26-2015, 11:23 AM

Hi Ross,

I still need your precious help for my text file coming from the data in the Worksheet.
The text file contains the return line after the total record, I want this return line automatically deleted when creating texte file. How can I do ?

Regards.

Create a txt file from the data in the excel sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet [Solved]

Re: Create a txt file from the data in the excel sheet

Re: Create a txt file from the data in the excel sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Create txt file from excel sheet

Automatically create file and fill in three cells from data in csv or other excel file

Create a *.txt file from an excel sheet

Filter, copy filtered data into another sheet, create a file of that sheet..

Issues with how to create a Macro that organize data from one excel file into another file

Bookmarks

Bookmarks

Posting Permissions