Date format to dd-mm-yyyy in VBA code

[hansgrandia]

Hello,

Despite the below standing code (Private Sub Process_XLS_Files) copies a date like 2-1-2017 it turns it into 1-2-2017. I have tried to get it fixed by add in an extra piece of code, but that did not help. Both with function of Dateformat as Numberformat.

Look forward to your reply. Hans Grandia NL

 'Date format to "dd-mm-yyyy"
    Sheets(1).Delete 'voorblad
    For Each wksht In Workbooks
    Rowcounter = Application.CountA(Range("A:A"))
    Range("A2", "A & Rowcounter").NumberFormat = "dd-mm-yyyy"
    Next wksht

Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
       
    Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
    Dim wksht As Worksheet
    Dim Masterfile As Workbook
    Dim Rowcounter As Long
        
    Set Folder = Fso.GetFolder(folderPath)
    
    For Each wksht In ActiveWorkbook.Worksheets
    For Each Subfolder In Folder.SubFolders
        For Each File In Subfolder.Files
            If InStr(File.Name, ".xls") Then
                Workbooks.Open (File.Path)
                    On Error Resume Next
                        Worksheets(wksht.Name).Select
                        If Err.Number = 0 Then
                            Range(Range("A14"), Range("A14").End(xlDown)).Select
                            ActiveCell.CurrentRegion.Select
                            Selection.Copy
                            ActiveWorkbook.Close
                            Worksheets(wksht.Name).Activate
                            Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
                            ActiveSheet.Paste
                         Else
                         On Error GoTo 0
                         ActiveWorkbook.Close
                        End If
            End If
        Next
    Next
    Next wksht

 

[Fluff]

Try

    Range("A2:A" & Rowcounter).NumberFormat = "dd-mm-yyyy"

 

[Joe4]

Changing the format of a cell does not change its actual value, it just changes how it is presented.
If the data is incorrect, you will actually need to convert it (and the number formatting will not do that).

Are you, perchance, using a European version of Excel?

Also note, in your loop, since you are not selecting each sheet as you loop through them, nor are you including the sheet reference in your range references, all the code is only being applied to the active sheet, and not each individual sheet. You either need to select each sheet as you loop through it or include the sheet reference in with your range reference. You also have a few other issues with how you wrote that loop. Here is it, cleaned up:

    For Each wksht In [COLOR=#ff0000]Worksheets[/COLOR]
        Rowcounter = [COLOR=#ff0000]wksht.Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
        [COLOR=#ff0000]wksht[/COLOR].Range([COLOR=#ff0000]"A2:A" & Rowcounter[/COLOR]).NumberFormat = "dd-mm-yyyy"
    Next wksht