Parsing data into a workbook, date / time errors | Excel forum

Resolved · Low Priority · Version 2003

Thu 15 May 2008, 17:05 Delegate Sarah said...

Sarah has attended:
Excel VBA Intro Intermediate course
Access VBA course

Parsing data into a workbook, date / time errors

Hi folks!

I'm designing a workbook which parses data from a comma separated (.csv) file into a new worksheet within the workbook then formats the sheet.

The code I have is mostly succesful at parsing the data, but unfortunately where I have two columns of date and time stamps eg. dd/mm/yyyy hh:mm:ss, they are copied across in US date format rather than U.K.
US date formats are mm/dd/yyyy hh:mm:ss.

The main problem with this is that the second date time stamp column may be in a different place depending on the data in the .csv file, so I can't tell the code to look in the same place and adjust the date with the DateSerial function.

How can I get the code to look at the type of data between each comma, determine it is a date, and parse it into the workbook in the correct U.K format?

Thanks for your help!

Fri 16 May 2008, 11:17 Delegate Sarah said...

RE: Parsing data into a workbook, date / time errors

Never mind folks, I fixed it.

For anyone stuck doing something similar, here's my solution.

Instead of trying to write code to check the dates on parsing in the data I recorded a macro using Excel's 'Import External Data' function in the 'Tools' menu.

From there it was fairly easy to specify the filename as a separate variable and modify the code to work with my project. It seems ok importing the dates without modifying them to U.S format.

If you are fortunate to know which columns will always contain dates, you can specify them in the import wizard.

If anyone is working on a similar project and would like to see the code I used, please get in touch.

Training courses