Extra subroutine to crop values of one column to 10 characters

[Retroshift]

Hi,
I have this macro (see below - code scripting credited to member rlv01) and I would like to add a subroutine which crops all the values of column O to 10 characters only.
So I want to only keep the first 10 characters of the data in every cell of the single column O. For example: if the macro imports "16/05/2022 11:00:00" into a cell in column O, the amount of characters should get cropped down to only the date (10 characters in this case) and not the time, so "16/05/2022".
And when a value of "16/05/2022" gets imported (merely the date (10 characters)), it is fine and it can stay there without any VBA action to it.
I am not a VBA virtuoso so any help with the scripting would be appreciated.

Sub RefreshClearCopyColumnsData()
    Dim wkBk As Workbook, wkBk2 As Workbook
    Dim wkSht As Worksheet, wkSht2 As Worksheet
    Dim FullPathName As String
    Dim ColArr, ColLetter
    Dim rng As Range
    Dim I As Long, lastrow As Long
    
    'First refresh the open workbook to update all the values
    
    Set wkBk = ActiveWorkbook
    wkBk.RefreshAll
    
    'Then clear the contents of the worksheet in the open workbook
    Set wkSht = wkBk.Worksheets("Sheet1")
    wkSht.UsedRange.ClearContents
    
    'Then get columns data from closed workbook into open workbook
    'Get path and Closed Workbook filename from (indirect?) cell A5 in open workbook
    
    FullPathName = wkBk.Worksheets("Sheet2").Range("A5").Value
    With CreateObject("Scripting.FileSystemObject")
        If Not .FileExists(FullPathName) Then
            MsgBox "File does not exist - abort"
            Exit Sub
        End If
    End With

    Set wkBk2 = Workbooks.Open(FullPathName)
    Set wkSht2 = wkBk2.Sheets(1)
    lastrow = wkSht2.UsedRange.Rows.Count
    
    'Specify the columns until the last row with data
    ColArr = Array("A", "O", "R", "V", "AD", "AG", "AH")
    
    For Each ColLetter In ColArr
        With wkSht2
            I = I + 1
            If I = 1 Then
                Set rng = Application.Intersect(.UsedRange, .Range(ColLetter & "1", .Range(ColLetter & lastrow)))
            Else
                Set rng = Application.Union(rng, Application.Intersect(.UsedRange, .Range(ColLetter & "1", .Range(ColLetter & lastrow))))
            End If
        End With
    Next ColLetter
    
    wkSht.Activate
    rng.Copy wkSht.Range("C2")
    wkBk2.Close False
    
    'Add message box
    MsgBox "Data successfully imported"
End Sub

 

[johnnyL]

Assuming they are text entries
How about:

  Columns(15).Replace " *", "", xlPart                  ' Columns(15) = Column O ;)

 

[Retroshift]

I am not sure if they are text entries: they have a custom format (yyyy-mm-dd hh:mm).

Where exactly could I integrate the code

Columns(O).Replace " *", "", xlPart

in the same sub?

 

[johnnyL]

Yes, right before the end or basically where you would have wanted to run an additional sub routine.

 

[Retroshift]

Yes, right before the end or basically where you would have wanted to run an additional sub routine.

I copied the code right before "End Sub" but it does not crop the values to 10 characters. It does not seem to do anything.
Here is some more information: the first cell of column O has a text title (e.g. "final deadline") and the other cells in this column are dates+times which are custom formatted like 2022-05-16 12:00 (the formula itself for this example cell is 16/05/2022 12:00:00).

 

[johnnyL]

If you make Column O wider, are the dates and times located on the left side of the column or on the right side of the column?

 

[Retroshift]

The text title (column header) is located on the left side of the column O but the dates and times are located on the right side of the column O.

 

[johnnyL]

Ok, so they are not text. Try the following:

    Columns("O").NumberFormat = "yyyy-mm-dd"

 

[Retroshift]

I tried it. It does not work. Does it have to do anything with the sheet maybe?

 

[johnnyL]

Which sheet are you wanting it to work on? I don't recall you mentioning that.