EXPORT TO EXCEL - time become date? (1 Viewer)

[Onlylonely]

Hi All,

Appreciate if you could help me out of this .
Excel time column should have the time exactly with the access table but it doesn't turn up to be the same.

 Dim db As Database
     Dim qdf As TableDef
     Dim rs As Recordset
     Dim oApp As Excel.Application
     Dim oBook As Excel.Workbook
     Dim xls As Excel.Worksheet
     Dim J As Long
     'Dim xls As Object    'your Excel.Application with sheet activated
     
     Set oApp = New Excel.Application
     oApp.Visible = True

     Set oBook = oApp.Workbooks.Add
     Set xls = oBook.Worksheets(1)
     
     Set db = CurrentDb()
     Set qdf = db.TableDefs("Receivingtbl")
     'Set qdf = db.QueryDefs("SupplierScrapQ")
     Set rs = qdf.OpenRecordset
     
     With xls
     
    .Name = "Database"
    .Cells.Font.Name = "Calibri"
    .Cells.Font.Size = 11
    
    .Columns("A").ColumnWidth = 12
    .Columns("B").ColumnWidth = 12
    .Columns("C").ColumnWidth = 12
    .Columns("D").ColumnWidth = 12
    .Columns("E").ColumnWidth = 12
    .Columns("F").ColumnWidth = 12
    .Columns("G").ColumnWidth = 12
    .Columns("H").ColumnWidth = 12
    .Columns("I").ColumnWidth = 12
    .Columns("J").ColumnWidth = 12
    .Columns("K").ColumnWidth = 12
 
    
    

         For J = 0 To rs.Fields.Count - 1
             .Cells(1, J + 1).Value = rs.Fields(J).Name
         Next
         .Range("A2").CopyFromRecordset rs
     End With
     rs.Close

 

[pbaldy]

I suspect you just have a formatting problem. What value is actually in the cell? Format that column as time and see what happens.

 

[Onlylonely]

HI Paul,

In excel it showing 1/0/1900 4.12.37pm
But is there a way to directly change it to time without put the formating in excel?
Example amend the code so that it can pull the data (TIME) from the table.

 

[pbaldy]

Date/time values are stored as double; what you're seeing is a date value of zero plus the time. You can push a time string to Excel by using the Format() function in your query:

Format(FieldName, "Short Time")

 

[Mark_]

While it is a formatting issue in excel, do you have a NEED for it to be stored as a time instead of text? Is this being exported for another application to import?

Paul's advice will definitely help your end users see the time, but knowing what the end goal is for this data can help avoid giving advice that could cause problems in the future.

 

[Onlylonely]

Date/time values are stored as double; what you're seeing is a date value of zero plus the time. You can push a time string to Excel by using the Format() function in your query:

Format(FieldName, "Short Time")

While it is a formatting issue in excel, do you have a NEED for it to be stored as a time instead of text? Is this being exported for another application to import?

Paul's advice will definitely help your end users see the time, but knowing what the end goal is for this data can help avoid giving advice that could cause problems in the future.

Thanks Paul / Mark.
Mark you are right. Instead of putting the time why don't I put a text and export it out? LOL. Thanks for the idea.

 

[The_Doc_Man]

Here is what is happening. As PBaldy said, in Access, date/time values are actually stored in a single field. Most of the pre-defined date/time-related formats are date AND time, or just date. For all of those times, what you are actually storing is the fraction of a 24-hour day that the time represents. The time is still in there, but most of the named formats won't reveal it. To convert that single DATE variable, you often need a custom format. For instance, FORMAT$( variable, "hh:nn" ) for a 24-hour clock time. Or you could try FORMAT$( variable, "Long Time" ) (or "Medium Time" or "Short Time") to use a named time format.