Using Excel Table as an ODBC connection in Crystal Reports

[crystal123456]

I am trying to use an Excel table as an ODBC connection but I am having trouble adding the datasource in Crystal and I am getting an error saying "Logon Failed, Details: HY000 The Microsoft Jet database enginer cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data"

I will explain more as to how everything is currently set up

1) We have a client computer on our network that the file is stored on. This file is always opened on this computer as it is used to collect data from another program. I have added a named range within this file that contains the data that I am looking to pull into Crystal. The version of Excel on this client computer is Excel 2010

2) I am trying to access the data and write a crystal report on my own computer which has Excel 2003. I have gone into the ODBC connections of my computer and tried to add the named range as a connection and that is where I am getting the above error message.

Is there another way I should be going about this to make this work properly?

Any help is greatly appreciated!

Thank you

 

[hilfy]

The problem is that the file is locked by the copy of Excel that has it open for editing. When you try to open the file in Excel, it will tell you it is locked and give you the option to open it in read-only mode. However, with an ODBC connection you don't have that option. Is it possible to work from a copy of the file?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[crystal123456]

Thanks for the response. I might be able to have our IT department set something up to automatically make a copy and save it on our server once a night or something like that.

I was just curious if there is any way to get around having to do this

 

[NickieP]

You can create an Access DB, create a 'linked table' to pint to the spreadsheet. In CR, change the database location to point to the MSAccess Table instead.
If you have Access 2007, you need to ensure you add the drive where the spreadsheet is stored to the 'Trusted locations' in MSAccess.
I have many spreadsheets set up like this due to the same issue you have.

 

[crystal123456]

Interesting, thanks for the tips. We don't have access on our terminal server, I wonder if any free programs out there could help with with that?