• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

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,
0
EWHTLC
Asked:
EWHTLC
3 Solutions
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
EWHTLCAuthor 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
 
EWHTLCAuthor 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
 
EWHTLCAuthor 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
 
EWHTLCAuthor 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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now