Accessing Excel via ODBC for Crystal Reports - causing issues 2

[SBendBuckeye]

Hello All,

I am trying to use an Excel file as a data source for Crystal Reports via ODBC. When I try to set up a DSN, everything shows up properly and I can create one (say ExcelTest). When I try to use it within Crystal Reports, the ODBC selection dialog appears and I can browse to a valid Excel file. But when I click OK, it comes back with nothing.

I noticed that the highest Excel version in the DSN was Excel2000 so I am wondering if I need to download?? newer DSN drivers from Microsoft? I'm a little fuzzy on the whole process. Has anyone else encountered this situation, and if so, how did you resolve it?

Thanks for any ideas and/or suggestions!



Have a great day!

j2consulting@yahoo.com

 

[scottostanek]

I use the excel functionality and there is a step missing.
You must also go into one of your tabs in the Excel file and create a named range. Goto "Insert | Name | Define". Give this a name etc and save the file.

Go back to Crystal and when you open the DSN you created that name will be a "table" for you to query on. Remember to include headers if you want to see named fields in the range. And it doesn't matter a lick if the range goes way beyond the real data --this if you are generating the data via Excel Qry or somesuch and the number of records returned may change.

I used this to allow me to union two differently formatted tables in Progress, using Excel VBA to convert rows with many buckets(columns) into separate rows for each bucket, merged that with a couple of other queries onto a single "data tab" and then report against it. Works much nicer than building all those durned formulas to total up the buckets conditionally.

YMMV
Scotto the Unwise

 

[SBendBuckeye]

Thanks Scotto! That works great!

Have a great day!

j2consulting@yahoo.com

 

[SBendBuckeye]

I found out later that if you save your Excel file as a CSV file instead of as an Excel file that Crystal will read it correctly without the intervening step. Good LucK!

Have a great day!

j2consulting@yahoo.com