Link to home
Start Free TrialLog in
Avatar of tkerschen
tkerschenFlag for United States of America

asked on

Crystal reports 2011 best choice for data sources

We picked up a copy of Crystal Reports 2011 (version 14.0.4.738 RTM) to produce a specific report that included data from 4 different spreadsheets and a couple of formulas. Crystal reports is installed on a Windows 7 pro 64 bit machine. MS office is not installed on that machine, (if that matters). Crystal reports refused to work when I tried using "xlxs" files. I had no problems getting results using Excel 97-03 workbooks (.xls). Now I have a new project that has a little twist. One of my data sources is a spreadsheet that is split up into a series of worksheets (one sheet for each letter of the alphabet), because if it were all on one sheet it would exceed the number of rows allowed by the 97-03 version of Excel. I of course want to insure I use a data source that is compatible with Crystal reports 2011. The project would require that the data be refreshed (reloaded and re-imported) every week or so. Would my best bet be to import the individual sheets into an Access (version 2003) database?
Avatar of Mike McCracken
Mike McCracken

Actually you can use Crystal with xlsx or other Office 2007 file formats.

YOu just need to download the Office 2007 ODBC drivers from MS

https://www.microsoft.com/en-US/download/details.aspx?id=23734
Download them and install them.  Then you can create ODBC connections to MS Office 2007 files.

Rather than trying to use 26 Excel worksheets, I would import the data into an Access database

mlmcc
Avatar of tkerschen

ASKER

I did find an excel macro that will combine multiple excel sheets into one, so if I open my massive spreadsheet in Excel 2007 or better I can save it as an xlsx file. The link you gave appears to be outdated, but I googling "Office 2007 ODBC drivers" led me to download a file named "AccessDatabaseEngine.exe" from Microsoft's site. Is that the correct file?
I don't think that is what you want

Try this link
https://www.microsoft.com/en-us/download/confirmation.aspx?id=27835

They didn't issue new ODBC drivers after Office 2007
You want this file
accessruntimeanddataconnectivity2007sp3-kb2526310-fullfile-en-us.exe

mlmcc
Need to also create a Named Range for the data in the slsx file in order for the ODBC driver to recognize it.
Found and tried to install the file accessruntimeanddataconnectivity2007sp3-kb2526310-fullfile-en-us.exe but it said "There are no products affected by this package installed on this system."
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I do not have office installed on the machine with Crystal on it. That's kind of where I was going with my question. Would I be better off installing Office 2003 and importing my "too big for excel 2003" spreadsheet into Access 2003 or installing Office 2007 and just using excel 2007 for my data source?
Personally I would get the data into a database rather than leaving it in spreadsheets.  I think your performance would better.

You could use Access 2003 or 2007.  Both can work with mdb files.  If you install Office 2007 or later then you could install the 2007 drivers and use the new accdb format.

mlmcc
I went ahead and put Office 2007 on the machine with Crystal already installed. I then had to familiarize myself with how to create a new data connection via OLE DB (ADO) rather than the Access/Excel (DAO) which I had used previously. The book I picked up, "Crystal Reports 2011 for Developers" didn't go into much detail on making data connections, but I found a Youtube video that explained it nicely: https://www.youtube.com/watch?v=UaruMg_SEc0
Looks like it's going to work with either Access or Excel 2007.
Thanks, guys.