Crystal Reports: Insert date in XLS file when quering

[sudkool]

Hello,
I am working on Crystal reports XI, and facing a problem. I will really greatful if you can give me some suggestions.

I have two excel sheets that my report is going to pull data from. Lets say one is book1 and other is book2. Both excel sheels have same number/type of columns. I started from using following command

select * from [c:\book1.xls].[Sheet1$]
union
select * from [c:\book2.xls].[Sheet1$]

Everything worked great and then I got another requirment. The are going to insert date with file names, so no the file names will look like
c:\book12006-09-30.xls and c:\book22006-09-30

Date will be last day of last month. So for suppose if we are running the report today, the date would be 2006-09-30.
I am not sure how to insert that forumla in the command I am writing. Could someone please help me.

Thanks in advance.

 

[Madawc]

You're asking how to get Crystal to read a file whose name varies? I don't think it allows that, at least not Crystal 10.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Jww193]

I'd attack this a different way and just backup/rename the excel files into something that's fixed in the the report.

Could be done with a simple script every morning etc.

 

[sudkool]

Hello,
I working on a report in which I have to fetch data from Excel sheet and use it in a Crystal Reports XI. Everything is working great except one problem. If the cell is type DATE in Excel sheet then crystal report is not able to recognize it and gives me blank. Not sure how to fetch Date from Excel sheet. Can someone please help me.

Example:
1. I Connect to desired Excel sheet through Acces/Excel (DAO) connection type in Crystal Reports.

2. In the Add command box I wrote following script
select 'sample'.'F1' as Name,
'sample'.'F2' as Address,
'sample'.'F3' as Birthdate
from 'Sheet1$' 'sample'

When I run it, it excute without any error, but when the data is fetched, the Birthdate column is null bcaz it is of type DATE.

Please help.