Using Access 2003 vba to send report to excel

Posted on 2008-11-18
Last Modified: 2013-11-28
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,
Question by:EWHTLC
    LVL 119

    Assisted Solution

    by:Rey Obrero
    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
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    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"
    LVL 77

    Expert Comment

    stop using shortdate as the format and use an explicit format of dd/mm/yyyy

    Author Comment

    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.
    LVL 77

    Expert Comment

    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.

    Author Comment

    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
    LVL 77

    Accepted Solution

    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.

    Author Comment

    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.

    Author Comment

    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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now