VBA to create Tab-delimited text file with Number Formats

Flowris

New Member
Joined
May 1, 2019
Messages
5
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:

Code:
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? :confused:

Your effort will be very much appreciated!

Floris
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Upvote 0
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.
 
Upvote 0
Welcome to the Board!


Try this

Code:
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.
 
Upvote 0
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.
 
Upvote 0
That was to be expected, so let's play a little.

Adjusts the format for columns M, N, O


Code:
    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
 
Upvote 0
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.
 
Upvote 0
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

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

By:

Code:
[COLOR=#333333]select case c[/COLOR]
 
Upvote 0
Thanks, it works!

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

Code:
   Case 12 'L
                        DataEx = Format(ExpRng.Cells(r, c).Text, "[$-13]dd mmmm yyyy;@")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top