Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA-Worksheet Name length issue while Querying frm another E
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA-Worksheet Name length issue while Querying frm anotherE
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA-Worksheet Name length issue while Querying frm anoth
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 "norie" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Odd issue with an Excel worksheet using macros | Excel Programming | |||
Excel Worksheet Password Issue!!!!! | Excel Discussion (Misc queries) | |||
validation list length issue | Excel Programming | |||
Querying Excel with SQL | Excel Programming | |||
Web Querying from excel. | Excel Programming |