Exporting date fields to excel, but not received as a date field.

[MushroomKing]

Hi guys!

I got a little issue with exporting.
When i export to an xls file, and one of the fields is a date field, excel will not format this as a date field.

Any ideas why or how i can solve it?

Help is much appriciated. Stuck here.

Sub XferData2XL()
 DoCmd.SetWarnings False
Dim sFile As String
Dim xl As Excel.Application
Dim rst As dao.Recordset, rst1 As dao.Recordset
Dim lngLast As Long
'strSQL = "SELECT KPICOLLECTIVE.* From KPICOLLECTIVE"
With CurrentDb.QueryDefs("KPICOLLECTIVE")
    'first parameter
   .Parameters(0) = [Forms]![stats_form]![startdate]
   .Parameters(1) = [Forms]![stats_form]![enddate]
    Set rst = .OpenRecordset
    ''''''rst1.Filter = "RegistrationDate=#" & Format(Me.startdate, "dd/mm/yyyy") & "#"
    ''''''Set rst = rst1.OpenRecordset
End With
sFile = "C:\filetest.xls"
'Set rst = CurrentDb.OpenRecordset(strSQL)
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open sFile
    .Range("A1").Select
    '.Selection.End(xlDown).Select      'goto bottom of data
   lngLast = .Range("A" & xl.rows.Count).end(xlUp).Row
    If lngLast = 65536 Then
        MsgBox "Sheet is full"
        GoTo ExitSub
    End If
    Range("A" & lngLast + 1).Select
    '.ActiveCell.Offset(1, 0).Select    'next free row
    .ActiveCell.CopyFromRecordset rst  'paste data
    .ActiveWorkbook.Save
    .Quit
End With
ExitSub:
Set xl = Nothing
Set rst = Nothing
 DoCmd.SetWarnings True
 DoCmd.Close acQuery, "KPICOLLECTIVE"
End Sub

 

[Frothingslosh]

As a rule, when something isn't working, we need to know exactly what is happening. You don't just tell a doctor that you don't feel well or a mechanic that your car isn't running right and leave it at that; the same applies here.

Are you just getting a number in Excel? Or is it treating it as text? Is your data itself saved in Access, SQL Server, or another database? How SPECIFICALLY is the data saved in that back end? (For example: in SQL Server, Date and DateTime are two completely different data types, and are handled differently by Access.)

 

[Gasman]

My question would be 'are they actually dates'? as I created the same code to help you in another thread and I get both date and times in both the expected columns of my excel sheet when I run the code

As a rule, when something isn't working, we need to know exactly what is happening. You don't just tell a doctor that you don't feel well or a mechanic that your car isn't running right and leave it at that; the same applies here.

Are you just getting a number in Excel? Or is it treating it as text? Is your data itself saved in Access, SQL Server, or another database? How SPECIFICALLY is the data saved in that back end? (For example: in SQL Server, Date and DateTime are two completely different data types, and are handled differently by Access.)

 

[MushroomKing]

Hey Gasman! Thanks again.

Well, it displays it right in excel. It looks like a date.
But when i check the format it's "general".

The query that is being exported, takes the date from a form field.
In the query i formatted the field as dd/mm/yyyy
But it does not seem to export as a date format.

In my query i have

Date: ([Forms]![stats_form]![startdate])

They need it like that for further processing.

 

[Mark_]

What type of variable is the "Date" stored in for the table the the query is based on? Is it a Date/Time?

 

[MushroomKing]

Hi Mark,

Its not from a table. The query takes the date from the form field.

Date: ([Forms]![stats_form]![startdate])

 

[Mark_]

It looks like what SHOULD be exported is RegistrationDate, correct? The only place you are referencing StartDate is in your code to set a filter.

I am just trying to work through what exactly you are putting into the field that is not coming across as a "Date".

 

[Gasman]

If you apply a format to it, it becomes a string.?
Try removing the format.
My table has a Date/Time fields and shows as Date format in Excel.

If you have to format it at all I would do it in Excel after the transfer.
A simple record macro will give you the code for that.


HTH

Hey Gasman! Thanks again.

Well, it displays it right in excel. It looks like a date.
But when i check the format it's "general".

The query that is being exported, takes the date from a form field.
In the query i formatted the field as dd/mm/yyyy
But it does not seem to export as a date format.

In my query i have

Date: ([Forms]![stats_form]![startdate])

They need it like that for further processing.

 

[The_Doc_Man]

Here is the issue with Excel.

The contents of a cell are closer to what Access calls a variant data type. Any cell. ALL cells. It is true that Excel has explicit data types such as LONG, BOOLEAN, DATE, STRING, etc. - but those are in VBA context, not in the cells of the worksheets.

Note also that you can "anchor" something to a cell, such as a chart or graphic or OLE object, but that is treated like a cell attachment, not an actual value. The value of a cell is cell-address.Value but a drawing object is in a different property of the same cell - AND they can co-exist.

Like Access controls that display values, it is sometimes possible to get the .Value of the control OR to get the .Text of a control - and they can be different, since .Value can be numeric but .Text is ... well, text.

The format-related properties of a cell will define how that variant is displayed but not what is actually stored. "General" format is actually the instruction to Excel to "make your best format guess." When you use the "format cells" option, you are supplying format information only. The cell values DON'T have to exactly match the format. (E.g. a number with multiple decimal places can be displayed as Currency, limiting itself to two decimal places. But if you revert that cell to General, the extra places come back (i.e. were never lost.)

Therefore, I would worry less about seeing format "General" on a column or in a cell as long as it displays correctly.

 

[Frothingslosh]

Yeah, Doc nailed it. There's nothing wrong with your export - the 'General' formatting option is Excel's default (open a new workbook and check if you don't believe me), and it's literally just instructions to let Excel display it in the format it thinks is best.

Like he said, as long is it displays fine, leave it be. If you need a SPECIFIC date format, then that can be done in your VBA code by setting the Format property of the specific columns, but in this case I think it's largely a waste of time, since you can do the same thing by hand after you create the worksheet in just a few seconds.