PDA

View Full Version : Help with code to export excel to text file with number format



brunor
05-02-2016, 12:02 PM
This code exports an excel 2013 file to text but displays the number format with a decimal. I would like to export the file and specify a number format with no decimal.
Current output:
Date Number
20160429 500.1234

Desired Output:
Date Number
20160429 500 (no decimal places)

I would appreciate any suggestions.


Sub ExportTXT()
Dim ExpRng As Range
Open ThisWorkbook.Path & "\TXT.txt" For Output As #1
Set ExpRng = Worksheets("Sheet1").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
Data = Data & vbTab & ExpRng.Cells(r, c).Value
Next c
Print #1, Data
Next r
Close #1
End Sub

Leith Ross
05-03-2016, 01:53 PM
Hello brunor,

Try this version of your macro.


Sub ExportTXT()

Dim Cell As Range
Dim Data As Variant
Dim ExpRng As Range

Set ExpRng = Worksheets("Sheet1").Range("A1").CurrentRegion

Open ThisWorkbook.Path & "\TXT.txt" For Output As #1
For r = 1 To ExpRng.Rows.Count
Data = ""
For c = 1 To ExpRng.Columns.Count
Set Cell = ExpRng.Cells(r, c)
If IsNumeric(Cell) Then
Data = Data & vbTab & Fix(Cell.Value)
Else
Data = Data & vbTab & Cell.Value
End If
Next c
Print #1, Data
Next r
Close #1

End Sub

brunor
05-04-2016, 03:17 AM
Hello Leith.
I can't thank you enough for your help. Your code works perfectly.

Bruno