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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
0
Patrick MatthewsCommented:
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
xlApp.Quit
Set xlApp = Nothing

rs.Close
Set rs = Nothing

MsgBox "Done"
0
peter57rCommented:
stop using shortdate as the format and use an explicit format of dd/mm/yyyy
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Eric HarrisDeveloperAuthor Commented:
mathewspatrick
I thought about doing it this way but wanted to try and avoid having to open excel as an object.

capricorn1
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.


Peter57r
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.
0
peter57rCommented:
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.
0
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
0
peter57rCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

Peter57r.
What you say makes sense. I'll try that as soon as I get a chance.
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.