Number in decimal Format in text file generate from Excel VBA

[mamun_ges]

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.

 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

 

[Joe4]

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]

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

 

[Alex Blakenburg]

I am sure that there will be more elegant solutions but this should work:

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

 

[mamun_ges]

Thanks for responsive

but the above code does not work as expected.

 

[Alex Blakenburg]

Thanks for responsive

but the above code does not work as expected.

Can you elaborate ? What is it giving you, that is not as expected ?

This is what I am seeing at my end.

 

[mamun_ges]

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]

Does your data start in A1 ?
I think your picture indicates D1

 

[mamun_ges]

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]

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.