exporting data from access to excel using vba

[Guest]

Hi there,

I'm exporting a datasheet generated by a query in access to excel worksheet
using VBA. the problem is that i have a 6 to 7 time fields in the datasheet,
but when i export the data into excel, it converts into date format.

for eg, if the data is "16:00:00" it comes up like "01/01/00 16:00:00". can
anyone please help me with this problem

 

[Douglas J. Steele]

In actual fact, you don't have time fields in Access, as Access doesn't have
a time-only data type.

The Date datatype is intended for timestamps: values with both a date and
time. The actual data is stored as an 8 byte floating point number, where
the integer part represents the date as the numbers of days relative to 30
Dec, 1899, and the decimal part represents the time as a fraction of a day.
When you supply only a date, Access supplies a "year" value of 0,
corresponding to 30 Dec, 1899. You can easily prove this by using a format
on your time field that shows both date and time.

I doubt very strongly that there's any workaround possible to solve your
problem. You'll have to remove the dates in Excel.