Using Access 2003 vba to send report to excel
Posted on 2008-11-18
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_cc, , _
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,