Crystal 8.5 Linking to Excel HELP!!

[Kallen]

Hi everyone,

I am using Crystal 8.5 and am having a HUGE problem.

I am trying to link an Excel spreadsheet to use as the basis for my report. I tried doing this in a variety of ways.

I try and create a new datasource, find my spreadsheet in Excel and than use it in the report, and nothing happens (aka, I can't find the file at all under the data explorer in Crystal).

I also try doing this as OBDC, I go through the steps, it looks as though I have the data, but when I try and generate a report, all I get are the table names, and not the data!!!

I am fairly new to Crystal, but have always had no problems connecting to Access tables. I need to base my reports on Excel for this project though because the users of the reports are dependent on Excel and most do not know Access.

Could this have something to do w/ it being 8.5?

Any suggestions or adive would be MOST appreciated

A humber beginner

 

[Darshu]

Hai friend
if u r using cr8.5 then u can use th export option.this should help u

 

[dgillz]

Make sure in your excel spreadsheet it "looks" like a database. No white space at all between column headings and the records.

I have never had a problem connectiong to either access or excel, and I have been using crystal since version 5. I currently use 8.5

Let me know if you still are having problems Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[Kallen]

Thanks to both for your reply.

I do not have any white space on the spreadsheet as it is basically a table (from an ASP) that gets automatically downloaded into an Excel file in CSV format.

I think my problem could be in how I am trying to connect to my datasource. I am having problems doing this and can't nip it yet (I bought two Crystal Books, both of them a wealth of info. but they really did not touch on what I needed to know most which was how to conncect to data sources, ugh).

This is what I am doing:
1. go into data explorer; create a new data source; I chose File Data Source [Machine Independent]

2. select a driver for which you want to set up a data source, I chose Microsoft Excel Driver [*.xls]

3. Location of data source-I chose Excel Files [Not Sharable].dsn

I then get the OBDC Microsoft Excel Set-up Form
I chose my workbook, say "OK", and them pooph, it's not there. The name of the worksheet is. when I click on what I thought was my spreadsheet in the data explorer I get the old "no items found".

Is the above procedure that way to connect to a datasource?

Thanks for you help in this very frustrating matter.

 

[dgillz]

This should make no difference, but setup your datasource in control panel rather than in Crystal's data explorer. I never use CR to set up a data source, I always use control panel. Try it and see what happens.

Also, you mentioned this is a CSV file. Is it being saved as an excel file? Definately revisit this.

Finally, you could email me your excel file and I can take a look at it. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[Kallen]

Thanks, I will do what you said about connection at the control panel level. I did not even know you could do that, so I appreciate the tip.

As far as the datasource coming into Excel as a CSV, I am a little concerned about that.

Should I have users keep it as a CSV file and not an XLS file? Most of the users are accustomed to saving their csv's as xls.

Thanks for your help, I am going to try your suggestion on the control panel this afternoon

 

[kenhamady]

Did you create a named range within the spreadsheet?
Highlight each table area you are reporting on within the spreadsheet (including the column headings/field names) and define each as a named range. Then save the spreadsheet and try again. These should show up as tables. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[lhouse2]

I am probably overlooking something but here goes.

If Crystal Reports is loaded on a "machine" that also has Excel loaded on it you can send the data from Excel to Crystal Reports. An "Add In" is added to Excel and Access if Crystal is loaded after(MS Office)

Go to tools ==> add ins and check the box for seagate crystal report wizard. It will add a new toolbar.

As stated just follow database design priciples when setting up the spreadsheet (no empty rows or columns) and name the range including headings. Click the Seagate tool bar to start the wizard and when prompted for the range type in the name you gave it.

Hope this helps

 

[reply]

An alternate route:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q257819&ID=KB;EN-US;Q257819

Set up a UDL file:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\test.xls;Extended Properties=Excel 8.0;Persist Security Info=False

and have at it.

 

[Kallen]

Thanks for all of the responses.

I have thought it over and decided that I am going to use an Access Database as my table source (the database will reside on the server.) The only downfall is that this is another database that I will have to manage, and get tons of phone calls a day on from the users of the reports.

This makes most sense (especially because I want to use look-up tables and it is a lot easier doing that in Access than Crystal (at least from my experience). This will also allow me the chance to manage the data better.

I will keep all of your responses for futre reference, Just think, I went from not having a clue to how to link to an OBDC data source to having many!!!!!

I actually did try naming the ranges, using the Excel add-in and setting up a UDL file, and all seemed to work much better than before.

I think for ease though, I am going to go back w/ what I am most comfortable with which is Access.

Thanks for all of the help this week everyone.