Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Parsing data into a workbook, date / time errors
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Parsing data into a workbook, date / time errors
Resolved · Low Priority · Version 2003
Reply Thu 15 May 2008, 17:05Delegate 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!
For upcoming training course dates see: Pricing & availability
Reply Fri 16 May 2008, 11:17Delegate 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.
|