Make Text to columns refresh happen using VBA excel 2010

0
votes

I have tried creating a macro that automatically formats a column value to change date and time just to date, however when the macro is run the cell values dont automatically update.

I currently need to make the following function selection happen using VBA for excel 2010.

Data/Text to Columns/Next button (keep default "Delimited")/Next button (keep default "tab")/Next button (keep default "General"/Finish button

I have tried recording functions but the VBA doesn't work. HELPPPPPPPPPPPPPPPPPPPPP!!!!!

Here is my code so far.

' Autochange_Extractdata_Date_Format Macro ' Sheets("Data Extract").Select

Columns("AK:AK").Select

Selection.NumberFormat = "m/d/yyyy"

Columns("AK:AK").Select

Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

2
excelvbarefreshcell
Are you trying to preserve the time in a separate column? – ARich
No im just trying to refresh the column to allow the cell content to be displayed in the new changed format. – Adrian Gornall
So to clarify, what isn't working, exactly? The format should be setting correctly from what I see. – ARich
it doesnt refresh the cell so that the new format is dispplayed it keep the old format until you enter the cell and refresh the cell – Adrian Gornall
What is the cell's format before you run the macro? Is it formatted as text? Is there a leading apostrophe? – ARich

2 Answers

2
votes

From the symptoms you described it sounds like your field is initially in TEXT format, that's why changing it to number format doesn't do anything. When you click into the cell it automatically converts it to a number.

Try this

Columns("AK:AK").NumberFormat = "m/d/yyyy"
Columns("AK:AK").Value = Columns("AK:AK").Value '//This will convert each properly

Columns("AK:AK").Select

Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
0
votes

Because i couldnt work out how to update the cell value i managed to work this code to change the cell value instead from Date time just to Date.

works a treat!!!

' Autochange_Extractdata_Date_Format Macro

Sheets("Data Extract").Select
Columns("AK:AK").Select
Selection.NumberFormat = "m/d/yyyy"
Range("AK4:AK999999").Select

Dim c As Range

For Each c In Selection
    Dim v As String
    v = c.value

    If c.value <> Empty Then
        c.value = DateValue(c.value)
    Else
        Exit For
    End If
 Next
 Range("A1").Select

End Sub