Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 export/import datetime
I have an xmlMap with a datetime field mapped to a listColumn, named "Time",
formatted as "[hh]:mm". My app requires that the "Time" column be in the range "04:00" - "27:59" - no date. Obviously, anything after midnight will have a date of 1/1/1900. I export one workbook using this map to an xml file and the xml file says: <Time1899-12-31T13:00:00.000</Time or <Time1900-01-01T02:00:00.000</Time When I try to import the xml file into my vba workbook the 1899 date is an invalid date, so it imports it as text. My workaround is to make both of these workbooks use 1904 date system. I know, bad idea. But, I am the only one using these 2 workbooks (at least until I die or retire) and they don't reference other workbooks. So, I figure I am relatively safe for the moment. This works fine until I do listColumn("Time").find on a time greater than 23:59. VBA doesn't recognize anything after "23:59" as a valid date, so I have to subtract 24 from the string and then do a DateAdd("d",1,time) function. Unfortunately, DateAdd doesn't know that my workbook is using the 1904 date system, so I have had to create my own AddADay function. This all works, but I hate kludgy workarounds. So, I continue to search for a better answer. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 export/import datetime
Hi KWarner,
The time peiode 04:00 - 27:59 looks odd, but you might need it. From the top of my head I suggest just before exporting to XML subtract 4 hours from the values in the Time column. After export add those 4 hours. Also after imprting the values add 4 hours to these impoted values. HTH, Wouter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 export/import datetime
Wouter HM, thanks for your time.
27:59 = 1/1/1900 3:59 AM - our workday goes from 4:00 AM to 3:59 AM. I can't just have 1:00 AM be 1:00 AM because then it doesn't sort correctly - 1:00 AM needs to come after 11:00 PM. Therefore, 1:00 AM has to be 25:00. My xsd definition has the "Time" column as dateTime. So, when a time-only value gets exported (i.e. 16:47) it tacks the date on like this: "<Time1899-12-31T16:47:00.000</Time". Excel doesn't like the year 1899, so I am unable to import this as dateTime, it actually gets imported as text. I could add a day to all records before export and then subtract a day after import, but there are over 2000 records. This is just another kludgy workaround and my other kludgy workaround is faster, even though using 1904 date system is generally a bad idea. "Wouter HM" wrote: Hi KWarner, The time peiode 04:00 - 27:59 looks odd, but you might need it. From the top of my head I suggest just before exporting to XML subtract 4 hours from the values in the Time column. After export add those 4 hours. Also after imprting the values add 4 hours to these impoted values. HTH, Wouter . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 export/import datetime
I changed the "Time" column from dateTime to double and all is right with the
world. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 export/import datetime
I hate working with data and times in VBA because it is very clumbsy. I often have to do work around like you are doing. One thing that bothers me is your statement that VBA doesn't recognize time after 24:00. Since any time greater than 24 hoursa should be listed as Jan 2. From my experience what I found is if you declare your varaible as time VBA truncates the time to 24 hours. But is you declare the variables as single or double VBA will not truncate. I often have to switch between variables declared as single and time to get my VBA code to work properly. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189062 http://www.thecodecage.com/forumz/chat.php |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Developer tab - xml import/export functions | Excel Worksheet Functions | |||
Import/export netCDF files to Excel | Excel Discussion (Misc queries) | |||
Add Visual Foxpro 9 DBF import / Export to Excel 2007 | Excel Worksheet Functions | |||
import and export from SQL SERVER to Excel | Excel Programming | |||
import- export XML in excel without loosing XML format | Excel Programming |