Hello everyone,

I have a Macros that auto populates a list of files.
This is a typical files name : 2-3 , Sections - Cell #7 Earthwork , Landfill , ACI , 7777-030.1 , 1989 , 2
Once the list of files is in excel, I perform a text to column Macro, here is the code :

Sub Txt2Col()

' Txt2Col Macro


    Range("Table1[File Name]").Select
    ActiveWindow.SmallScroll Down:=-672

    Selection.TextToColumns Destination:=Range("I10"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub
The delimiter is the Comma, and the first column of my table, witch is supposed to be sheet # and show as 2-1, 2-2.... keep coming up as a dates.

This is what its giving me :
COMMADATEPROB.PNG

I have tried formatting all the cells beforehand, even only the cells in the column, but every single time i run the Macro, the cells I formatted at Text keeps changing to a Date/General format every time i run the Macro. This is extremely frustrating.

All the files are already named, so i would really like to be able to write something in my code the would stop the automatic formatting of the destination cells. Putting ' in front of the text would be extremely time consuming as there are a big number of files.

Thanks