Excel exportation (annoying problem with time!!!)

[696796]

Hello everybody,

I'm not sure if this is an access thing or excel, so i'm just gonna go for it.

I am exporting a forms contents to an excel spreadsheet via a macro in the form. Now when i export this data, the Time column i have exports a date (00-Jan-00) for every record where it should come out as time such as 00:12:33.

Now in excel i can change the format of the column to:-
[h]:mm:ss (this is in custom in the Number tab when you change the columns format).

I have tried to make an excel template to save the output to, but the same data just comes back.

The data in the tables format is Date/time so i guess this is why its coming out in this way, but i need to input a time so dont think i can change this.

does anybody know what i can do to solve this annoying problem!

Regards,

Alex

 

[SUnderwood]

Consider it solved ;-)

Exporting from Access to Excel will cause this h:m:s problem for the reason you have stated.

To get around it you'll have to do the following.

1. In your query use "format([Date Field],'dd/mmm/yyyy')". Note, use "mmm" to ensure no date conversion problems.
2. Export the query as an .csv file
3. Use automation to get Excel to open the .csv and save it as a .xls

We have to export as a .csv becuase the format of our [Date Field] is text and Access will want carry fwd the field format to each cell in an Excel file (hence we'll get h:m:s if we set it to date/time).

Automation will force Excel to interpret the date field as a date, and set the field type correctly. This however does mean that large numbers may be reformated to 1.9-E10 etc. but u'll play with it and work out the finer details.

Hope this helps

Sean Underwood




Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
seanunderwood1@hotmail.com

http://www.emergencydatabasesupport.port5.com

 

[steveg0702]

Sean,

I have a somewhat related question: How do I export a query to an open Excel or CSV file? I have many users that I want to have access to queries I've built and I'd like them to have their results generated in Excel or CSV (preferable Excel) because they're not very comfortable with Access. I've only been able to find examples of how to export to a saved file.

Thanks,
Steve

 

[keywest1]

Hi everybody,

I am exporting data from Lotus Note to excel file format. I experience a problem with data format or data type. The number format got converted to scientific notation format. For examle: 123456789 got converted to 1.E0+... How do I create a VBA or codes that will convert it text format?

Thanks,