Excel auto converting uk Bank sort Codes to date

Varidian

New Member
Joined
Jan 27, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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)

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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,998
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
hi

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

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

Varidian

New Member
Joined
Jan 27, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
hi

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

Columns("A:A").NumberFormat = "@"
yeah, that was my first thought,

VBA Code:
 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

New Member
Joined
Jan 27, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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 :)
 
Solution

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,998
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,468
Messages
5,831,809
Members
430,087
Latest member
meagerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top