Text to Columns Wizard

[excelnoobnoob]

Hi,
I have a data connection to a csv in my excel workbook.
One of the columns contains dates in mm/dd/yy format.
Excel won't accept 'format as date', so I searched online and found a solution (use the Convert Text to Columns Wizard).
This works! The column is now formatted correctly as date.
The only issue is, whenever the data source refreshes, the column reverts to the original, general format.

Does anybody know what can be done to resolve such an issue?

 

[Takae]

How about using a sheet event, for example, assuming that the data source has been updated, it means A1 has changed. Then running the code executes.

Please paste below code on the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else
        'call or paste here your code
    End If
End Sub

 

[excelnoobnoob]

How about using a sheet event, for example, assuming that the data source has been updated, it means A1 has changed. Then running the code executes.

Please paste below code on the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else
        'call or paste here your code
    End If
End Sub

Thanks Takae - I *think* cell A1 doesn't change when the source has been updated. (when refreshing, rows are just added to the bottom of the table).
I could be wrong tho.

I've tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("TLog[[#Headers],[Column1]]"), _
        DataType:=xlFixedWidth, FieldInfo:=Array(0, 3), TrailingMinusNumbers:=True
    End If
End Sub

But on data refresh it isn't being triggered.
I looked at some alternative methods (detecting new row on table - which is tricky/affects other sheets from most examples I've seen)
Did you have any ideas?
Thanks so much for your help

 

[excelnoobnoob]

EDIT: I also tried changing:

If Intersect(Target, Range("A1")) Is Nothing Then

to:

If Intersect(Target, Range("C2")) Is Nothing Then

Since this is the column where the formatting is reverting. I would have assumed that when the data refreshes, this cell changes (the value doesn't change, but the format does).

Still doesn't execute tho

This macro does work:

    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("TLog[[#Headers],[Column1]]"), _
        DataType:=xlFixedWidth, FieldInfo:=Array(0, 3), TrailingMinusNumbers:=True

It just isn't being triggered for whatever reason.

 

[Takae]

If the data just add rows, how about this one.
It will be executed when new data is added somewhere from column A.　If the leftmost column is column C, set it to C.(It doesn't have to be the leftmost column though.)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then' or C:C? This line is trigger.
        Exit Sub
    Else
        Application.EnableEvents = False
        'call or paste here your code
    End If
    Application.EnableEvents = True
End Sub

 

[excelnoobnoob]

I can feel im getting closer! Maybe...
Now when I hit refresh, I get an unexpected function or variable message.

This is the code for reference:

 

[Takae]

Are you going to run another macro of another book? This code run after you update the data.
"Application.Run...." this line should be a code to change the format.
I thought you already had the code to change the format. If you don't have one, please let me know which column you would like to change.
And I want to make sure your goal is "mm / dd / yy".

 

[excelnoobnoob]

I thought I did have the code but it does not work ( I generated by recording a macro)
The column is C, and I’m trying to set it to mm/did/yyyy like choosing MDY in text to columns does.

 

[excelnoobnoob]

Are you going to run another macro of another book? This code run after you update the data.
"Application.Run...." this line should be a code to change the format.
I thought you already had the code to change the format. If you don't have one, please let me know which column you would like to change.
And I want to make sure your goal is "mm / dd / yy".

Sorry forgot to mention the macro I am running is in the same book

 

[Takae]

I put code for changing format after "Else". So, if you refresh the data as usually, this macro runs automatically and changes format on Col C.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long, i As Long, x

    If Intersect(Target, Range("C:C")) Is Nothing Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Range("C:C").NumberFormatLocal = "mm/dd/yy;@"
        LR = Cells(Rows.Count, 3).End(xlUp).Row
        x = Range(Range("C2"), Cells(LR, 3))
        For i = 1 To UBound(x)
            x(i, 1) = CDate(Format(x(i, 1), "##/##/####"))
        Next
        Range(Range("C2"), Cells(LR, 3)) = x
    End If
    Application.EnableEvents = True
End Sub