Need help in changing date from dd/mm/yyyy to dd-mmm-yyyy from Macro

[amandeep08]

I have one Macro but the date format is coming as dd/mm/yyyy. I want to change the same to dd-mmm-yyyy.
Below is the coding:


Dim i As Double
Dim j As Double
Dim wholeLine As String

Dim aWF As WorksheetFunction
Set aWF = Application.WorksheetFunction

Application.ScreenUpdating = True

ThisWorkbook.Worksheets("menu").Range("D10").Value = "Current Record"
ThisWorkbook.Worksheets("menu").Range("G10").Value = 0

i = startRow

Do While i <= noOfRows
j = 1
ThisWorkbook.Worksheets("menu").Range("G10").Value = i
If i > startRow Then
wholeLine = wholeLine & vbNewLine
End If

Do While j <= noOfCols
If j > 1 Then
wholeLine = wholeLine & "^"
End If
If IsDate(Trim(aWF.Clean(SourceWB.Worksheets(sheetName).Cells(i, j)))) Then
wholeLine = wholeLine & makeString(Day(Trim(aWF.Clean(SourceWB.Worksheets(sheetName).Cells(i, j)))), 2, "0", True) _
& "/" & makeString(Month(Trim(aWF.Clean(SourceWB.Worksheets(sheetName).Cells(i, j)))), 2, "0", True) _
& "/" & Year(Trim(aWF.Clean(SourceWB.Worksheets(sheetName).Cells(i, j))))
Else
wholeLine = wholeLine & Trim(aWF.Clean(SourceWB.Worksheets(sheetName).Cells(i, j)))
End If
j = j + 1
Loop
i = i + 1
Loop
Call ExportToTextFile(outputFileName, wholeLine, False)

MsgBox "File Conversion Successful"

errorHandler:
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Description
Exit Sub
End If

End Sub

 

[Trevor G]

Try this out, adjust the column letter or letters if necessary

Sub ChangeDateFormat()
Columns("G").NumberFormat = "dd-mm-yyyy"
End Sub