Hi John,
Thanks for your interest.
I'm importing named ranges of a Lotus 123 spreadsheet in
VBA code using the TransferSpreadsheet method of the
DoCmd command. The way I was able to see which ranges
could be seen by Access was to manually use the Import
Spreadsheet Wizard.
This spreadsheet has a large number of named ranges, many
of which are single cell ranges that seem to use the "Use
Label" option where the range name is in a cell just to
the left of the named range. Others do refer to formulas
or lines of macro code. Still others have blanks in the
range name. Perhaps they are not available to Access.
The named range that was giving me the problem however,
referred to a contiguous block of cells. However, the
range name contains embedded blanks. My first thought
was that this was the problem, but when I saw so many
other range names that didn't show up in Access, I was
left stumped...so I posted the original query.
This morning I tried creating a new, additional, name over
the problematic range, substituting the underscore charac-
ter for the embedded blanks, and found out that I could
see the range name in Access.
As you point out, apparently, there are a variety of
reasons why Access might not be able to see a range name
in a Lotus 123 spreadsheet. Because the ranges in this
one fell into a variety of categories, I didn't know where
to turn at first.
With your help, I found out how to circumvent my problem.
Thanks again.
-----Original Message-----
Hi Chuck,
I haven't worked with 1-2-3 for many years so am rather guessing here.
What do the ranges that Access "cannot see" have in common? (Working
with Excel, for instance, Access can only import named ranges that refer
to a single rectangular block of cells, not ones that refer to formulas
or non-contiguous cells.)
Can 1-2-3 export these ranges successfully?
How are you getting the list of 1-2-3 ranges into Access?
I've written some VBA code in MS Access to import a named
range from a Lotus spreadsheet into an Acess table. This
works fine, with the following exception: Access cannot
see all of the named ranges in the Lotus Spreadsheet.
At first I thought the problem might be occurring because
the range name has embedded blanks. However, when I
verified this by manually tring to import the spreadsheet,
I could see that many range names were missing that did
not have embedded blanks.
Has anyone run into this problem and come up with a
solution?
Thanks.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
