Data Connectivity: CR 2011 Connection to 2010 Excel

Print Page | Close Window

CR 2011 Connection to 2010 Excel

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17405
Printed Date: 03 May 2024 at 6:31pm

Topic: CR 2011 Connection to 2010 Excel

Posted By: vcs1161
Subject: CR 2011 Connection to 2010 Excel
Date Posted: 23 Aug 2012 at 10:19am

In the Crystal 2008 version I was able to connect to an Excel spreadsheet through DAO and I now see I can't do that in the 2011. Is there an updated driver I need to have to make this work? Has anyone else come across or used this connection before? I have existing reports that are set up this way and now need to connect to the new .xlsx files.

Replies:

Posted By: kevlray
Date Posted: 23 Aug 2012 at 10:38am

I know that you can export to Excel 2007-2010, but I have not heard if you can use Excel 2007-2010 as a data source. Maybe through an ODBC connection.

Posted By: vcs1161
Date Posted: 23 Aug 2012 at 10:50am

I have been doing it that way for quite some time with earlier .xls file types through DAO with no problems. So how do I set up a ODBC connection? I tried it and it is requiring a user name and password. There's no way to bypass something like that? Does that have to be set up through an administrator?

Posted By: vcs1161
Date Posted: 23 Aug 2012 at 10:59am

I just tried it now and connect to an Access database just fine like I have before.

The latest version I get for Excel to connect this way is Excel 8.0 from my list of choices which lets me connect to earlier excel versions but not the new .xlsx version.

Posted By: kevlray
Date Posted: 23 Aug 2012 at 11:02am

I remember seeing a posting on some forum about not being able to use Excel 2007-2010 as a data source. But I do not know if they ever found a workaround. Wish I knew more about it.

Posted By: kevlray
Date Posted: 23 Aug 2012 at 11:24am

I just created an ODBC connection to an Excel 2010 spreadsheet (I had to choose the correct driver from the list, Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb). It appears to work okay. Of course if you cannot use an ODBC driver, then I do not have any other suggestions.

Posted By: z9962
Date Posted: 24 Aug 2012 at 12:02am

The only way to connect to xlsx is though ODBC using the Excel driver.

CR 2011 even though supports exporting to xlsx does not allow a direct connection as a source.

If you do not have permission to ODBC then this is an issue. Does it have to be a xlsx file? can it be xls or csv?

Posted By: vcs1161
Date Posted: 24 Aug 2012 at 2:54am

It's xlsx because the number of rows in it exceeds what xls would accept and users still use it and refer to it. The reason why I am trying to connect to it like I easily can to .xls is to create Crystal reports from it and automate them through a Business Objects environment.

Posted By: z9962
Date Posted: 24 Aug 2012 at 3:09am

Ok I have just had a look for you, see how this goes...

Create a new connection

Select OLE DB (ADO)

Select the Microsoft Office 12.0 Access Database Engine... Provider

Change the Office Database type to Excel

Select the data source.

Leave userId Password etc as is.

That should work, I have not used in the past, but did a quick test and seems to be working of for me.

Posted By: vcs1161
Date Posted: 24 Aug 2012 at 3:26am

I can create a report from the csv so I may need to come up with a procedure to copy this as a csv each month that this file is updated. And yet the end users will still use the .xlsx file.

Posted By: dfolzenlogen
Date Posted: 05 Jul 2013 at 2:58pm

Per Daniel Paulsen at sap.com:

"support for Office 2007 file formats as datasources was added in an early service pack for CR XIR2. details can be found at:

http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/105142f1-8f1e-2b10-ddac-dbbd038ba53b?quicklink=index&overridelayout=true]

FYI -- I downloaded from Daniel's suggested webside and, even though I use MS Office 2010, it enabled me to work with xlsx files using OLE DB (ADO) quite easily.

Try it and see if it will work for you.

Dixie

Posted By: adavis
Date Posted: 16 Jul 2013 at 8:48am

Originally posted by z9962

Ok I have just had a look for you, see how this goes...