VBA to create Tab-delimited text file with Number Formats

[Flowris]

Hi,

I'm new to this Forum as to VBA and Macro's. I'mm hoping you can help me with the following since I'm not finding any copy/paste solution. Currently I'm working on an excel file that serves as input for InDesign. My challenge is to automate the following:

Excel formulas help me to fill a table that with a macro button export the sheet to a tab-delimited file.
Currently I've the following script:

Sub WriteToTextFile()Dim ExpRng As Range
Dim myTab As String
Open "/......./.....txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Set ExpRng = Range("A1").CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1
    For r = FirstRow To LastRow
       Data = ""
       For c = FirstCol To LastCol
           If c = 1 Then myTab = "" Else myTab = vbTab
          ' Data = ExpRng.Cells(r, c).Value
           If c = FirstCol Then
            Data = Data & ExpRng.Cells(r, c).Value
            Else
            Data = Data & vbTab & ExpRng.Cells(r, c).Value
            End If
       Next c
       Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Data
   Next r
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
MsgBox "file has been generated"
End Sub

It works but the problem is that the date is not copied in the defined Format and that the currency sign is lost in several columns. Could you please help me to make the script complete?

Your effort will be very much appreciated!

Floris

 

[Joe4]

Welcome to the Board!

You can use the FORMAT function in VBA to specify the format you want to export particular values in.
See: https://docs.microsoft.com/en-us/of...format-function-visual-basic-for-applications

Another option is to just set up the sheet the way you want, and then save it as a Tab Delimited Text File.
Creating the file that way will keep all the formatting you have applied to the sheet.

 

[Flowris]

Thank you for you answer. What you suggest is what I currently doing and that takes time, because I have to reopen the sheet as it opens the textfile. Is there a way you can help me to modify the code with the formatting functionality, if necessary the formatting can be column based as is the amount of columns. Looking forward to your response.

 

[DanteAmor]

Welcome to the Board!


Try this

Sub WriteToTextFile()
    Dim ExpRng As Range
    Dim myTab As String
    Open "c:\trabajo\tst.txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Set ExpRng = Range("A1").CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1
    For r = FirstRow To LastRow
        Data = ""
        For c = FirstCol To LastCol
            If c = 1 Then myTab = "" Else myTab = vbTab
            ' Data = ExpRng.Cells(r, c).Value
[COLOR=#0000ff]            frm = ExpRng.Cells(r, c).NumberFormat[/COLOR]
[COLOR=#0000ff]            DataEx = Format(ExpRng.Cells(r, c).Value, frm)[/COLOR]
            If c = FirstCol Then
                Data = Data & [COLOR=#0000ff]DataEx[/COLOR]
            Else
                Data = Data & vbTab & [COLOR=#0000ff]DataEx[/COLOR]
            End If
        Next c
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Data
    Next r
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    MsgBox "file has been generated"
End Sub

Check the result, if it is not desired, then we can put the format in specific columns.
Tell me in which column you have the date and the currency and what format you want.

 

[Flowris]

Thank you DanteAmor,

It does not work completely I guess.
For currency it is _($X.XXX,XX_) instead of Euro currency €X.XXX,XX. in column M, N, O.
For the date it is put down in the right form, however it was translated from Dutch on the sheet to English in the text file for Column L.

 

[DanteAmor]

That was to be expected, so let's play a little.

Adjusts the format for columns M, N, O

    Dim ExpRng As Range
    Dim myTab As String
    Open "c:\trabajo\tst.txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
    Set ExpRng = Range("A1").CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1
    For r = FirstRow To LastRow
        Data = ""
        For c = FirstCol To LastCol
            If c = 1 Then myTab = "" Else myTab = vbTab
            ' Data = ExpRng.Cells(r, c).Value

            select case c.column
                case [COLOR=#ff0000]13, 14, 15  [/COLOR]  'M, N, O
                      DataEx = Format(ExpRng.Cells(r, c).Value, "[COLOR=#ff0000]€X.XXX,XX[/COLOR][COLOR=#333333]"[/COLOR])
                case else
                     frm = ExpRng.Cells(r, c).NumberFormat
                     DataEx = Format(ExpRng.Cells(r, c).Value, frm)
             end select
            If c = FirstCol Then
                Data = Data & DataEx
            Else
                Data = Data & vbTab & DataEx
            End If
        Next c
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , Data
    Next r
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
    MsgBox "file has been generated"
End Sub

 

[Flowris]

Hi Dante Amor,

I get an error 424. Besides does it also solve the dutch translation for the date? Hope you see the reason why it causes the error right now.

 

[DanteAmor]

Hi Dante Amor,

I get an error 424. Besides does it also solve the dutch translation for the date? Hope you see the reason why it causes the error right now.

Change this

[COLOR=#333333]select case c.column[/COLOR]

By:

[COLOR=#333333]select case c[/COLOR]

 

[Flowris]

Thanks, it works!

for the date display in Dutch I added. Thanks for the help!

   Case 12 'L
                        DataEx = Format(ExpRng.Cells(r, c).Text, "[$-13]dd mmmm yyyy;@")

 

[DanteAmor]

That was the idea, to put the code such that you could put the desired format for each column.

I'm glad to help you. Thanks for the feedback.