Opening CSV File & It Removes Spaces From Date Columns?

[Philb1]

Hello
When I open a downloaded csv file with vba it shows the the date & time in the correct format for the month that appears at the top of the column. The column comes formatted in dd/mm/yyyy hh:mm. Scroll down to where the next month starts & the date & time appear as text on the left of the column, but still displaying almost the same. After looking at it I noticed that there are 2 spaces between the date & time for the top rows & where it changes month, one of the spaces is removed & it displays as text. Enter another blank space & it then displays correctly as a date. Open the csv file manually & this doesn't happen.
I've tried saving as xlsx & it's still the same scenario.
The vba to open it is Workbooks.Open Filename:= Path & Filename & I've tried Workbooks.Open(Path & Filename)
Both with the same result. As soon as it's open, it's ok for the latest month, after that & at the change of date between months, it displays as text
Any ideas why?

Thanks

[Pete_UK]

I think you need to open the file in Notepad then you will be able to look at the original data directly - can you deduce anything from that? (i.e. dates in a different format from what you are expecting)

Hope this helps.

Pete

[Philb1]

Hi Pete
I can open it & see it perfectly alright when I open it manually with a double click. It's when I open it with VBA the problem shows. It's a file of transactions I'm importing into a journal workbook. I have found a way round it, but it's a rigmarole. Open it with vba, then do a replace what " ", Replacement " " on the affected columns. Then I have to select all the cells one by one to simulate pressing enter & getting it to display as a date

[Philb1]

For anyone that's interested. The answer is to (via the ribbon data tab) import the csv file into excel, set delimiter as comma & date as DMY. Then it all appears with the date set properly and as a date, not text. After figuring that out, I recorded it as a macro & then tidied the code. Doing it this way opens any csv file properly.