How to fix date format in export to txt file?-CodePudding

I cannot crack why my code is not working when it comes to exporting data to notepad.

Code creates extra sheet, pastes range of data which should be exported to notepad and deletes the sheet after.

But it keeps pasting date in notepad in format DD/MM/YYYY, even when I specified no separators:

Worksheets("NotepadTransfer").range("F:F").NumberFormat = "DDMMYYYY"

Also, I have no idea how to keep the formatting of cells. So let say: column M displays result of formula. It is rounded to 2 places after comma. So it displays 22,00 - but the true value of cell is 21,989898 and the true value is copied to the export file, not the rounded one (which should be). Any ideas how to fix it?

Sub PricingZVOLtoNotepad()

    'Create 9F End file
    
    Dim c00            As Variant
    Const textFilePath As String = "C:\AAA\ZVOL_9F_End.txt"
    Dim lngCounter     As Long
    Dim FF             As Integer
    Dim LastRowZVOL    As Long
    
    LastRowZVOL = Worksheets("LSMW ZVOL MATERIAL").range("A" & Rows.Count).End(3).Row
    
    Worksheets.Add.Name = "NotepadTransfer"
    Worksheets("LSMW ZVOL MATERIAL").range("V3:AA" & LastRowZVOL).Copy
    Worksheets("NotepadTransfer").range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Worksheets("NotepadTransfer").range("F:F").NumberFormat = "DDMMYYYY"
    
    Worksheets("NotepadTransfer").Activate

    FF = VBA.FreeFile
    c00 = range("A1").CurrentRegion


    Open textFilePath For Output As #FF
        For lngCounter = LBound(c00, 1) To UBound(c00, 1)
         Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
        Next
    Close #FF
    
    Worksheets("LSMW ZVOL MATERIAL").Activate
    
    Application.DisplayAlerts = False
    Worksheets("Notepadtransfer").Delete
    Application.DisplayAlerts = True
    
    'Create LSWM file
    
    Const textFilePathLSMW As String = "C:\AAA\ZVOL 9F LSMW.txt"
    
    Worksheets.Add.Name = "NotepadTransfer"
    Worksheets("LSMW ZVOL MATERIAL").range("A3:T" & LastRowZVOL).Copy
    Worksheets("NotepadTransfer").range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Worksheets("NotepadTransfer").range("C:D").NumberFormat = "DDMMYYYY"
    
    Worksheets("NotepadTransfer").Activate

    FF = VBA.FreeFile
    c00 = range("A1").CurrentRegion


    Open textFilePathLSMW For Output As #FF
        For lngCounter = LBound(c00, 1) To UBound(c00, 1)
         Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
        Next
    Close #FF
    
    Worksheets("LSMW ZVOL MATERIAL").Activate
    
    Application.DisplayAlerts = False
    Worksheets("Notepadtransfer").Delete
    Application.DisplayAlerts = True
    
    MsgBox "ZVOL files have been created."

End Sub

CodePudding user response：

I believe your problem is caused by the .Numberformat() function: this does not change the value of a cell, it just modifies the way the value is shown.
In case you want to actually change the text of a cell, according to a text format (like "YYYYMMDD"), you might opt for the =TEXT() Excel function or the Format() VBA function.

CodePudding user response：

Your problem is:

c00 = range("A1").CurrentRegion

When you transfer a range to a Variant, it will transfer the .Value property, which is the date information in the windows regional short date settings format.

You will need to cycle through c00 to convert the dates to your desired format.

Something like:

for i = 2 to ubound(c00,1) 'assuming there is a header row
   c00(i,6) = Format(c00(i,6),"ddmmyyyy") 'assuming dates are in column F
next i

For the number formats, the cause is the same. But the fix is more difficult. You will have to either explicitly round the values in VBA, or transfer the text property of the range object to your array. To do the latter will require cycling through the worksheet column which will be more time consuming. It will also require that the columns be properly sized so as to show the entire value.

Consider just saving the file as a text file, and then opening it in notepad.