Eric Harris
asked on
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,
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,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
peter57r
stop using shortdate as the format and use an explicit format of dd/mm/yyyy
ASKER
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.
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.
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.
Just to add that this is not a general problem with Access exports to XL - I'm afraid it IS specific to you.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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