- Local time
- Today, 14:19
- Joined
- Feb 19, 2013
- Messages
- 17,558
I'm using a sub in ms Access to open an instance of Excel, which in turn opens a csv file. It then manages the data into a standard format (delete header rows, calculate some columns and change the sort order) to then save as a csv file for subsequent upload into an Access table.
The files are all csv or .html files of transactions downloaded from various banks. Each bank has a different format for file naming, order and names of columns, header information etc. Some have latest transaction at the top, others at the bottom and I have a a table containing various parameters for which rows to delete, whether to change the sort order, etc.
It all works well, except for one thing
If I open the file manually from excel, the data appears as it should. However if I use vba code (late binding)
Date columns are a mixture of text and numbers
and the numbers are based on the US format date. So the correct date is 1st July - but interpreted as 7th Jan - whilst the 14th July is correct - but text (which I can live with). I believe this is due to the assumption a date is in US format (1st July) unless it is unambiguous (14th July)
Any suggestions for resolving this? I've tried chatGPT, which didn't come up with anything useful. Basically I need to force the date column to be UK or SQL standard of yyyy-mm-dd - and obviously getting the correct date
The files are all csv or .html files of transactions downloaded from various banks. Each bank has a different format for file naming, order and names of columns, header information etc. Some have latest transaction at the top, others at the bottom and I have a a table containing various parameters for which rows to delete, whether to change the sort order, etc.
It all works well, except for one thing

If I open the file manually from excel, the data appears as it should. However if I use vba code (late binding)
Code:
'Start Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open the file
Set wb = xlApp.Workbooks.Open(srcFile)
Set ws = wb.Sheets(1)
Date columns are a mixture of text and numbers
and the numbers are based on the US format date. So the correct date is 1st July - but interpreted as 7th Jan - whilst the 14th July is correct - but text (which I can live with). I believe this is due to the assumption a date is in US format (1st July) unless it is unambiguous (14th July)
Any suggestions for resolving this? I've tried chatGPT, which didn't come up with anything useful. Basically I need to force the date column to be UK or SQL standard of yyyy-mm-dd - and obviously getting the correct date