Solved

CR2013: Excel 2010 xlsm as Datasource

Posted on 2014-04-09
7
1,864 Views
Last Modified: 2014-04-10
I just installed Crystal Reports 2013 on a Windows 2008 R2 server b/c you can't connect to excel 2010 workbooks using CRXI.  So, now I am having issue with my connectoin to my .xlsm sheet through CR2013.  I can connect to it, but it only shows me one of the many worksheets that exist in this workbook (14 worksheets altogether) and shows one named range.  So, all in all the worksheet that I need to connect to is the first worksheet and it not visible in the database expert (amoung most other sheets).

Below is the process i used to create a connection to the excel sheet.

1. I setup the System DNS using \\windows\syswow64\odbcad32.exe
2. I choose the Microsoft Excel Driver (*.xls, *.xlsx,*.xlsm, *.xlsb).
3. Then selected Database version Excel 12.0 and selected the workbook i wanted to point to (a .xlsm file)...I named the connection "Trade Blotter"
4. Then in CR2013 I click on Database > Database Expert and "Create New Connection" using ODBC (RDO), under Data Source Name I select "Trade Blotter" and hit finish.

Thanks for the help on this.
0
Comment
Question by:IO_Dork
  • 4
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39989352
Did you download the Microsoft Office drivers?
I had to download them for Office 2007 so I could use xlsx files with CR XI.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

These may work with Office 2013 or you may need to find similar drivers for Office 2013.  I can't find any on a quick search of MS.

To use them you have to create the ODBC connection as above using the driver for Office 2007 then use and ODBC connection.

I'll verify the steps when at home

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39989434
ok, but doesnt the fact that the system dns options list the following as a drive mean that I have them loaded:

Microsoft Excel Driver (*.xls, *.xlsx,*.xlsm, *.xlsb).
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39989640
I would think so.

Unfortunately I don't seem to have the drivers loaded on this machine.  I will check if I have that same issue at home tonight

mlmcc
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 39990518
I just tried in CR XI

I can connect using the Excel Files drive rto an xlsx and see all the tabs

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 39990529
So you were able to connect to a xlsx file using crxi?  That's weird as SAP says the new excel database connection in 2007/2010 is different from 2003 and xi was only designed to be able to read pre excel 2007...that it was a change in how excel works.  Also, when I try to connect to anything 2007 or higher, crystal says it has problem accessing/logging on to the database source.  So the only solution was to upgrade to cr2013 so that it could connect with xlsx,xlsm, and xlsb...but doing that and using odbc falls short.
0
 

Author Comment

by:IO_Dork
ID: 39990534
So to clarify, you connected by creating odbc in system DNS using excel drivers, not just a simple excel/access connection in CR?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39990599
Yes.  I downloaded the MS Office drivers I linked to above.  It adds a Excel 2007 driver to the ODBC drivers.  

SAP/Crystal is correct the native Crystal cannot connect to Excel 2007 xlsx files but using the driver available from MS it can.

I follow the same steps you seem to.  When I add the RDO connection to the report, I see all the tabs from the xlsx workbook

mlmcc
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now