Export to Excel with Time Values 1

[nat1967]

Hi everyone,

My Access query returns values from a table is a time format. (4:00 pm) However, when I export this query to Excel, the format in Excel changes to "DATE" and the values show up like (1/0/1900). Why? How can I get the values to show up as time as they are in the database table?

I have changed the format in the database table to all options (LONG, Med, short time). All formats end up (1/0/1900) in Excel.

Help please....

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Stretchwickster]

Try selecting the cells in question and select "Cells..." from the "Format" menu. Under the "Number" tab click the Time option in the list and select the specific time format you would like.

Let me know if you'd like the code for this.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[nat1967]

HI Stretchwickster,

I have been manually making the changes as you suggested and it does work. Unfortuately, I am trying to use the following code to export from Access to Excel with no manual intervention for the users. If you could show me how to format the cells so the time would show up correctly, you would be a god send to me.

'***code snippet******
With objXL
.Visible = False
Set objWkb = .ActiveWorkbook
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Activate
.Range(.Cells(2, 1), .Cells(conMAX_ROWS, intLastCol)).ClearContents
.Range(.Cells(2, 1), .Cells(conMAX_ROWS, intLastCol)).Font.Bold = True

.Range("A2&quot.CopyFromRecordset rs 'location the paste to excel starts

End With

'****end code snippet*******

The above code transfers just fine. I just cant seem to figure out how to format the cells afterwards. I would need all of the cells in the "C" column formated to 'Time'. Thanks for any help and suggestions!!!



Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Stretchwickster]

Use something like this:

Columns("C").NumberFormat = "h:mm AM/PM"

You may need to fiddle around with the specific time format. Try recording a macro of what you are trying to do manually. I often use it to find out what commands to use.

Hope this helps

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[nat1967]

Thanks Clive!!!

Excellent idea! I will try the macro concept tip in the future also but your answer works perfectly.

I am very much a novice in Excel and having some difficulty figuring out the object model.

Thanks again!

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Stretchwickster]

No problem man - macro recorder is definitely a tip worth remembering!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)