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)
It still treats it as a date.
this is doing my head in
any help would be much appreciated.
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)
VBA Code:
' 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.