Excel auto converting uk Bank sort Codes to date

[Varidian]

Hi all,

I have a problem that is causing me huge problems.
I get a CSV every day that I need to create a number of other sheets off, for inputting into over applications.

(The CSV is a report from another app)

a UK Sort code is in this format 10-10-10, however, if excel is able to convert this into a date on opening it does.

when I open the file in VBA and dump it into an array, (has this fixes all the extra line feeds in the file) however some of these sort codes are being converted to dates. If I then try to change the format to Text, the sort codes are then completely different numbers.

if I try to import this file using the excel data import CSV "Button" it treats the sort code correctly. and imports in the correct format.

if I use (I know its commented at the minute)

 ' With ActiveSheet.QueryTables.Add(Connection:= _
'        "TEXT;" & FileToOpen, Destination:=wsImport.Range("A1"))
'       .Name = "FILEOPEN...."
'        .FieldNames = True
'        .RowNumbers = False
'        .FillAdjacentFormulas = False
'        .PreserveFormatting = True
'        .RefreshStyle = xlInsertDeleteCells
'        .AdjustColumnWidth = True
'        .TextFilePlatform = 437
'        .TextFileStartRow = 1
'        .TextFileParseType = xlDelimited
'        .TextFileTextQualifier = xlTextQualifierDoubleQuote
'        .TextFileConsecutiveDelimiter = False
'        .TextFileTabDelimiter = False
'        .TextFileSemicolonDelimiter = False
'        .TextFileCommaDelimiter = True
'        .TextFileSpaceDelimiter = False
'        .TextFileTrailingMinusNumbers = True
'        .Refresh
'    End With

'    End With

It still treats it as a date.

this is doing my head in

any help would be much appreciated.

 

[SQUIDD]

hi

Have you tried formatting the column to text before you run your query on the recipient sheet.

Columns("A:A").NumberFormat = "@"

 

[Varidian]

hi

Have you tried formatting the column to text before you paste it

Columns("A:A").NumberFormat = "@"

yeah, that was my first thought,

 ThisWorkbook.Sheets("Import").Range("AY2:AY" & lastrow).NumberFormat = "@"

Its converting it has it opens the file. so the data getting loaded has already been converted to to a date.

 

[Varidian]

3 days on this issue!

and I just had an epiphany. when I looked at the last line of code for the 100th time sometimes you just need to have a chat with somebody else to change your mindset of the problem.

Thanks Squidd

ThisWorkbook.Sheets("Import").Range("AY2:AY" & lastrow).NumberFormat = "mm-dd-yy"

This formats back to the correct way, and I have checked it against several files.

Can I answer my own question

 

[SQUIDD]

lol

Glad you got it sorted, i have had these issues before myself, i was trying to dig out the file to see how i corrected it.

Dave