Using Access 2003 vba to send report to excel

A question that's been tinkered with a few times before I think but I don't seem to be able to find an answer.
I have an external customer who needs a daily extract generated in excel format which they use to load into their data base. I subsequently email the extract all in one using the following code.
( I have an Access adp with a SQL Server backend)

  DoCmd.SendObject acSendReport, "rep_Extract", acFormatXLS, _
  Me!str_send_to, _
  Me!str_send_cc, , _
  Me!str_subject, _
  Me!str_message_body, False

The report 'rep_extract' contains a simple detail line from a table.
The process works fine but I have a problem because my customer requires that dates are formated in dd/mm/yyyy format.

Even though my  fields are formated on that table and my report fields are also formated as 'shortdate' ,the actual excel spreadsheet is created the date columns are formatted as a number. (i.e the number of days since 1/1/1900).
This is subsequently being rejected by my customers import process.

Is there any way to intercept and format the columns programatically, or does anyone have any other ways to attack the same problem.

My knowledge of Access and SPQ is OK but I'm self taught. Any help would be greatly appreciated,
Eric HarrisDeveloperAsked:
Who is Participating?
peter57rConnect With a Mentor Commented:
I am guessing that the problem lies in this new piece of info...

"i.e the date base field is an nvarchar (10) containg 30/07/2008"

I am not sure of the SQL Server issues here, but I suspect that you need to do a proper conversion to a datetime field.

I don't know whether you can get away with using Cdate(fieldname) in your report or in the report's recordsource, but it seems worth a try.
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
transfer the record source of your report.
alter the date field to  Format([datefield],"mm/dd/yyyy")  so it will be passed to excel as string
Patrick MatthewsConnect With a Mentor Commented:
I would create an Excel template (XLT file) that has the proper formatting, including the date format, and
then use something like this:

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("NameOfQueryReportIsBasedOn")
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add("path to XLT file goes here")
Set xlWs = xlWb.Worksheets("name of destination worksheet goes here")

' assuming XLT has headers in Row 1...
xlWs.[a2].CopyFromRecordset rs

With xlWb
    .SaveAs "Save to path goes here"
    .Close False
End With
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Set rs = Nothing

MsgBox "Done"
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

stop using shortdate as the format and use an explicit format of dd/mm/yyyy
Eric HarrisDeveloperAuthor Commented:
I thought about doing it this way but wanted to try and avoid having to open excel as an object.

Sorry for being a bit thick but I don't quite get what you mean. You might need to give me a little more detail. My record source for thereport is a SQL stored procedure.

For some reason I can only set a shortdate formay for the fields if I make multiple selections. (i.e 3 fields all together). Individually I have nothing on the drop down list. Either way I'm not sure how to do and explicit format unless you simply enter "dd/mm/yyyy". With or without the quotes it just doesn't seem to work.
Yes, you enter dd/mm/yyyy (no quotes).

Just to add that this is not a general problem with Access exports to XL - I'm afraid it IS specific to you.
Eric HarrisDeveloperAuthor Commented:
sorry for the dealy. I've been trying a few thing.
I doesn't seem to matter what format I enter in the properties for a control on the report.
If I run the report the date is diplayed correctly.
if I send it using :
DoCmd.SendObject acSendReport, "rep_Extract", acFormatXLS, _
  Me!str_send_to, _
  Me!str_send_cc, , _
  Me!str_subject, _
  Me!str_message_body, False
All I get is the date displayed as a number.

i.e the date base field is an nvarchar (10) containg 30/07/2008
When I run the report it is displayed as 3-/07/2008
when I perform the extract to xl seems to autoformat and the field is displayed as 39659
Eric HarrisDeveloperAuthor Commented:
Sorry for the dlay everyone.
I stil have this problem but I've just been knee deep in other problems at the moment.
Things are manic

What you say makes sense. I'll try that as soon as I get a chance.
Eric HarrisDeveloperAuthor Commented:
managed to complete it eventually.
2 things really
I had to define the field in the SQL table as smalldatetime.
That then enabled me to define the firld on the report as shortdate.
Therest took care of itself really.
THanks for all your help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.