Number in decimal Format in text file generate from Excel VBA

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
Hi, I have searched a lot but find no answer. Hope someone helps me in this context.
I am using a vba code to create a text file from an excel sheet. The code works fine. But there is a number format in decimal in excel which converts as a number without a decimal place in the text file. below is the code I used.
VBA Code:
 Dim UsedRows As Long
 Dim UsedColumns As Long
 Dim R As Long, C As Long
 Dim Data    As Variant
  
 '// Define a suitable file name
 Open "E:\Folder\" & "TD.txt" For Output As #1
 With ActiveSheet
 UsedRows = .UsedRange.Rows.Count
 UsedColumns = .UsedRange.Columns.Count
 For R = 2 To UsedRows
 For C = 1 To UsedColumns
 Print #1, .Cells(R, C); "|";
                Set Cell = .Cells(R, C)
                    If IsNumeric(Cell) Then
                        Data = Data & Cell.NumberFormat = "#,##0.00"
                    Else
                        Data = Data & vbTab & Cell.Value
                    End If
 Next C
 Print #1, .Cells(R, UsedColumns)
 Next R
 End With
Hope someone helps me to overcome my difficulties and tell me what to change or add to the code.

Thanks in advance
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,111
Office Version
  1. 365
Platform
  1. Windows
Can you post an example of the issue?
What does the data look like?
How are you trying to write it?
How it is appearing?

Also, how exactly are you viewing your text file after it is produced?
Are you using a Text Editor (i.e. NotePad, WordPad, etc)?
You should NOT be using Excel to view it after-the-fact, as Excel may perform its own conversions on the file when opening it, so it is not a reliable tool to see exactly what the data in a text file looks like.
 

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
Thank You for your response. The data look like the image, the text file generates looks like capture2. But the required text file should look like the capture3 image.

I am using a notepad to view the text file. Any suggestion .

Thanks
 

Attachments

  • Capture 1.JPG
    Capture 1.JPG
    32.2 KB · Views: 8
  • Capture 2.JPG
    Capture 2.JPG
    24.5 KB · Views: 8
  • Capture3.JPG
    Capture3.JPG
    27.2 KB · Views: 8

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
I am sure that there will be more elegant solutions but this should work:

VBA Code:
Sub ExportTxt()

    Dim UsedRows As Long
    Dim UsedColumns As Long
    Dim R As Long, C As Long
     
    '// Define a suitable file name
    Open "E:\Folder\" & "TD.txt" For Output As #1
    With ActiveSheet
        UsedRows = .UsedRange.Rows.Count
        UsedColumns = .UsedRange.Columns.Count
        For R = 2 To UsedRows
            For C = 1 To UsedColumns
                Select Case C
                    Case 2              ' Numeric Column with 2 decimals
                        Print #1, Format(.Cells(R, C), "#.00"); "|";
                    
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C)
                    
                    Case Else
                        Print #1, .Cells(R, C); "|";
                    
                End Select
            Next C
        Next R
    End With

    Close #1

End Sub
 
Solution

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52

ADVERTISEMENT

Thanks for responsive

but the above code does not work as expected.
 

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52

ADVERTISEMENT

You are seeing exactly what I am expecting. But at my end amount of 6000 shows instead of 6000.00 in the text file.

It looks like the code is behaving differently in different machines.

Any suggestions.

Thanks.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
Does your data start in A1 ?
I think your picture indicates D1
 

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
Yes, my data starts from A2, A1 is the Header.
As you see in code "For R = 2 To UsedRows"
I just wanted to show in pictures the specific area of my problem is?
 

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
Yes, I found the fact, It works perfectly.
In my datasheet number is in 5 columns. So the case will be 5.

Thank you so much for the help.
 

Forum statistics

Threads
1,141,863
Messages
5,709,074
Members
421,613
Latest member
wyzco

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
Top