Cannot access Excel created w/ German version of Excel

[Ranko Lalic]

Hi all

I work on an app that creates an Excel file through automation. We then set
an ODBC User Data Source based on Excel driver and use Crystal Report to
generate some reports using the generated file as a data source. All this has
been working for a few years for a few hundred users w/o problems.

Last week, we had a user in Germany trying our product. Crystal Report was
failing w/ Failed to open rowset.

After a few days of debugging, I finally got him to send me his Excel file.
I had the same problem as him on my machine. I then try setting Data
Connection in Visual Studio 2005. While the connection seems to be created
OK, I neither can see the table (worksheet) nor the columns that are there.

So, that would indicate that neither Crystal Report nor VS 2005 can read
data from the Excel file through ODBC Excel driver.

If I open the file in Excel, it opens OK and I can see a worksheet and 2
columns with 2000+ rows that are there.

If I create a new Excel file on my machine, and then copy & paste all data,
the new file also works OK.

The original file was created on German version of Windows XP w/ SP2. It has
German version of Excel 2003 (11.8231.8221) w/ SP3.

I tried creating my User Data Source with “Driver do Microsoft Excel –
4.00.6305.00â€, “Microsoft Excel Driver – 4.00.6305.00†and “Microsoft Excel-
Treiber - 4.00.6305.00â€. Neither works with the user’s file.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\win32 is set to
C:\Program Files\Microsoft Office\OFFICE11\msaexp30.dll. I also tried w/
C:\WINDOWS\system32\msexcl40.dll. Neither worked.

I need only read access.

Any help is appreciated.

If you want a file, email me on (e-mail address removed) and I will send it to you.

Cheers
Ranko

 

[Ranko Lalic]

Hi all

I think that I have found the problem. To use Excel w/ ODBC driver, you have
to define a name for the region of the Excel workbook that will be used by
the driver. We create our Excel document from the template. The template is
using cell-naming syntax R1C1, as opposite to more widely used A1 syntax.

Unfortunately, in German it is not R1C1 but Z1S1. The Names Add function
does not generate exception but just silently fail so defined name that ODBC
driver is using is never set.

I am planning to use Address function on Region to get proper syntax. I hope
that I will get Z1S1 on the German system and R1C1 for the USA one. Has
anyone tried this before? Is there a better way? (I do not want to change
template to use A1 as I may break many other things doing this.)

Cheers
Ranko Lalić