Excel2000: Can't access an Excel table though ODBC anymore!

  • Thread starter Thread starter Arvi Laanemets
  • Start date Start date
A

Arvi Laanemets

Hi

I have an Excel file on shared network resource. I have several dynamic
named ranges defined there, and 2 fixed named ranges
Name1=Sheet1!$B$2:$F$2999
Name2=Sheet2!$A$1:$E$400

In another workbook, I have to query data from table Name2. When I created
the query, all worked fine, but now I discovered, that the query isn't
working when the source workbook is closed. With closed source workbook, I
can see only one named range - Name1 - as data source available.

I use ODBC queries quite often, and there never was such problem before, so
at moment I am at my wit's end! I'm waiting for some advice, how to clear
this problem.

Thanks in advance!
 
Hi

The problem is cleared!

I found out, that Excel ODBC can't access named ranges in closed workbooks,
when those names contain non-english characters. My name contained character
'ä' (NädalX) - renaming it and creating a new query solved the problem. At
same time umlaut characters in sheet name used in name definition, and/or in
field names included into query, didn't affect the query at all.
 
...
I found out, that Excel ODBC can't access named ranges in closed workbooks,
when those names contain non-english characters. My name contained character
'ä' (NädalX)
Click to expand...

This is not my experience. Both the following queries work with the
Excel ODBC driver:

SELECT * FROM VälidDefinedName;
SELECT * FROM [VälidSheetName$];

Jamie.

--
 
Hi

It's localized version of Office, is it? I have english one!


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


Jamie Collins said:
...
I found out, that Excel ODBC can't access named ranges in closed workbooks,
when those names contain non-english characters. My name contained character
'ä' (NädalX)
Click to expand...

This is not my experience. Both the following queries work with the
Excel ODBC driver:

SELECT * FROM VälidDefinedName;
SELECT * FROM [VälidSheetName$];

Jamie.

--
Click to expand...
 
Strange

For me, when I start creating a new query
(Data.GetExternalData.NewDatabaseQuery, DataSource: Excel Files*), and
select an Excel file as datasource, only named ranges which don't contain
umlauts in name, are displayed, when this Excel file wasn't opened before.
When the source file was opened before, all non-dynamic named ranges are
displayed, but the query don't work later, when the source file is closed
again.
 
Arvi Laanemets said:
Strange

For me, when I start creating a new query
(Data.GetExternalData.NewDatabaseQuery, DataSource: Excel Files*), and
select an Excel file as datasource, only named ranges which don't contain
umlauts in name, are displayed, when this Excel file wasn't opened before.
When the source file was opened before, all non-dynamic named ranges are
displayed, but the query don't work later, when the source file is closed
again.
Click to expand...

Just to confirm, my sheets and defined Names that contain umlauts are
all visible in MS Query.

Jamie.

--
 
Back
Top