Excel VBA-Worksheet Name length issue while Querying frm another E

[Paz]

Hi,
I have written a VBA code that queries SQL Server & another Excel sheet
using ADO; and then compare both the data to ensure if both are identical.
Everything (including querying DB & Excel; comparing the data) works fine,
except the following case.

As a limitation, MS Excel allows a worksheet to have a name of maximum
length 31. So, when I execute the following query, it goes fine.
Select * From [Portfolio Evaluation Worksheet$C7:C267]
where, 'Portfolio Evaluation Worksheet' is the worksheet name which is of
length 30.

The same query fails, when the Worksheet name reaches its maximum length
i.e., 31; say the following query
Select * From [Portfolio Evaluation WorksheetA$C7:C267]
where, 'Portfolio Evaluation WorksheetA' is the worksheet name and is of
length 31.

Why this is happening so?

As far I can think, it is the '$' which is creating the problem. The Excel
which can allow a maximum worksheet name length of 31, is considering the
range selection string '$' as well, which makes the total length to 32, &
hence Excel is unable to recognize!

Am I right? Is there a solution for this?

To reiterate, this happens only when the Worksheet name is of maximum length
31and doesn't happens when the length is 30.

As of now, I am trying to limit the length to 30, but it is a painful task
to keep the name length to 30.

Version: MS Office 2003
Os: Windows Xp

Thanks
Paz

 

[norie]

Do you really need such a long name for the worksheet?

Why do you use Worksheet in the name?

Surely it's quite obvious it's a worksheet.

 

[Paz]

Hi Norie,
The team which uses this Macro have created all the worksheets with names
related to the data that is available in that sheet.

These excel sheets are some test cases, & the macro is used to compare the
test data and the data generated by the application (which is stored in the
database) by querying both.

So, a workbook may contain many worksheets with different data based on the
scenario under test. Hence, a proper name is given to the worksheet instead
of keeping it as Sheet1, Sheet2 etc.

BTW, the current solution I have given is to keep the Worksheet name short.


Thanks
Paz