Solved: Max sheet tab name length

[agarwaldvk]

Hi Everybody

Does anyone know if there is a maximum length to the Worksheet name that is allowed when retrieving data from a closed workbook using Micosoft Access Jet 4.0 engine like so :-


sFilename = sourceModelName 'Workbook name with path
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
thisRangeAddress = Range(Cells(thisStateStartRow, startCol4DataRetrieval, Cells(thisStateFinalRow, productNamesFinalCol)).Address(rowabsolute:=False, columnabsolute:=False)
sheetAndRangeAddress = "[" & sourceBookSheetName & "$" & thisRangeAddress & "];"
Set rs = New ADODB.Recordset
sSQL = "SELECT * FROM " & sheetAndRangeAddress
rs.Open sSQL, sConnect, adOpenStatic, adLockReadOnly, adCmdText


All variables have been defined elsewhere in the program. This is only a part of the program.

I have been able to successfully work with sheet names of upto 30 characters but for some reason when it goes to 31 it is not able to find the worksheet object to access.

Any suggestions?


Best regards



Deepak Agarwal

[stanleydgrom]

agarwaldvk,

The maximum length for a tab name is 31 characters (Excel 2003, 2007).

Have a great day,
Stan

[agarwaldvk]

Hi Stan

If that is the case, then why doesn't it accept this name :-

AdjFacOtherValues1 - Years1And2


Any suggestions please!

We are using Office 2003


Best regards


Deepak Agarwal

[CaptRon]

I tried just renaming a sheet with the characters you provided and it worked for me in Excel 2003. I don't know why it won't work in your code.

Ron

[agarwaldvk]

Hi Ron

In that case, do I still need to use Microsoft Jet 4.0 or higher?

I have been using Jet 4.0 for Office XP applications. For Office 2003 and higher, what is Jet Engine version that works with 31 characters?


Best regards


Deepak

[rory]

That works fine for me using Jet 4. Are you sure there are no trailing or leading spaces in your sheet name variables?

[Norie]

Could it be that you have spaces in the worksheet name?

Try enclosing it in [] brackets.

[rory]

The whole statement is being enclosed in square brackets, which should be all that's needed:
sheetAndRangeAddress = "[" & sourceBookSheetName & "$" & thisRangeAddress & "];"

[Norie]

rory

Oops, never spotted that.:)

But perhaps that's the problem?

Shouldn't it only be the sheet name enclosed in square brackets.

[rory]

Nope, the syntax is fine - it's the same as I use. I tested it with a 31 character sheet name including spaces and it was fine, which was why I thought that possibly there were extra spaces in there.

[agarwaldvk]

Hi Guys

I am genuinely impressed!

The worksheet name "AdjFacOtherValues1 - Years1And2" is exactly how it is. THis adds upto exactly 31 characters.

As you all have suggested, this should work. I am at work tomorrow and I shall try it again and report back.

Its bewildlering that the same code works for some and not for others.

I really really am appreciative of your suggestions.

I am sure there has to be something not quite right with our setup at work. Maybe I will get home as well and test it on both Office XP and 2007 and what is the outcome. I do not have Office 2003.


Just tried it again at home on both Excel XP and Excel 2007. Works with neither when the sourceBookSheetName = "AdjFacOtherValues1 - Years1And2".

Works on both when the sourceBookSheetName = "AdjFacOtherValues - Years1And2".

Any further suggestions?

Rory, what did you do EXACTLY when it worked for you. Could you please send me your code that you tested with so that I can then try with your code.


Best regards


Deepak

[rory]

I used:
Sub GetData()
' Sample demonstrating how to return a recordset from a workbook
Dim cn As ADODB.Connection, strQUery As String, rst As ADODB.Recordset, strConn As String
Set cn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ado_test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
' With cn
' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .ConnectionString = "Data Source=C:\ado_test.xls;" & _
' "Extended Properties=""Excel 8.0;HDR=Yes;"""
'' .CursorLocation = adUseClient
' .Open
' End With
strQUery = "SELECT * FROM [AdjFacOtherValues1 - Years1And2$blah];"
Set rst = New ADODB.Recordset
rst.Open strQUery, strConn, adOpenStatic, adLockReadOnly, adCmdText
Range("A2").CopyFromRecordset rst
Set rst = Nothing
cn.Close
Set cn = Nothing
End Sub

Which version of ADO are you using?

[agarwaldvk]

Rory

Still doesn't work!

The version of ADO that I use is 2.6. But I even tried with 2.7. No success!


The surprising thing is that when, using your code, if I am not able to get it going, how did you get it going? Bizzare!!!!!!

This is what I used both ADO version 2.6 and 2.7.


Sub GetData()
' Sample demonstrating how to return a recordset from a workbook
Dim cn As ADODB.Connection, strQUery As String, rst As ADODB.Recordset, strConn As String, thisAddress As Variant
Set cn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyAdoTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
thisAddress = Range(Cells(1, 1), Cells(20, 13)).Address(rowabsolute:=False, columnabsolute:=False)'For test only!

strQUery = "SELECT * FROM [AdjFacOtherValues1 - Years1And2$" & thisAddress & "];"

Set rst = New ADODB.Recordset
rst.Open strQUery, strConn, adOpenStatic, adLockReadOnly, adCmdText
Dim test1 As Long, test2 As Long
test1 = rst.RecordCount
test2 = rst.Fields.Count
rst.Close
'Set cn = Nothing
cn.Close'Complains here!
Set rst = Nothing
End Sub




Deepak

[rory]

Sorry that was a hacked together version of what I used and I forgot to comment out a couple of the lines before posting (I use Connection objects usually). It should be:
Sub GetData()
' Sample demonstrating how to return a recordset from a workbook
Dim cn As ADODB.Connection, strQUery As String, rst As ADODB.Recordset, strConn As String
' Set cn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ado_test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
' With cn
' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .ConnectionString = "Data Source=C:\ado_test.xls;" & _
' "Extended Properties=""Excel 8.0;HDR=Yes;"""
'' .CursorLocation = adUseClient
' .Open
' End With
strQUery = "SELECT * FROM [AdjFacOtherValues1 - Years1And2$blah];"
Set rst = New ADODB.Recordset
rst.Open strQUery, strConn, adOpenStatic, adLockReadOnly, adCmdText
Range("A2").CopyFromRecordset rst
Set rst = Nothing
' cn.Close
' Set cn = Nothing
End Sub

[agarwaldvk]

Dear Rory

No luck still but I take it that you must be doing something that I am not.

For some reason - and I can't think of any - it just doesn't seem to work for me. I tried with exactly the same code but no luck!

I guess I will have to live to with it but it would have been nice to have it working - if it can work for someone then why not me?

Do I need any other references activated?


Best regards


Deepak

[rory]

Ah, found it - I was using a named range and you weren't. If I use your address string, I get the same problem you do. After a little digging, I found you can get round that by changing the syntax (In case named ranges aren't an option):
Sub GetLongSheetNameData()
' Sample demonstrating how to return a recordset from a workbook
Dim strQUery As String, rst As ADODB.Recordset, strConn As String, thisAddress As Variant

thisAddress = Range(Cells(1, 1), Cells(20, 13)).Address(rowabsolute:=False, columnabsolute:=False)

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ado_test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""

strQUery = "SELECT * FROM [AdjFacOtherValues1 - Years1And2$][" & thisAddress & "];"

Set rst = New ADODB.Recordset

rst.Open strQUery, strConn, adOpenStatic, adLockReadOnly, adCmdText

Range("A2").CopyFromRecordset rst

rst.Close

Set rst = Nothing
End Sub

HTH

[agarwaldvk]

Mate

You have to be an absolute legend.

That worked like a charm!

How in the world did you think of doing what you did? I was totally lost and didn't know which way to turn!!!!!

May be that is why you are an Excel MVP and I am not!!!!!!!!!!

Anyway, thanks a zillion!


Best regards


Deepak Agarwal

[rory]

3 words - "trial and error" :)
it was a question of trying various permutations of the syntax, using square brackets and/or ` delimiters. More luck than judgment though!

[unmarkedhelicopter]

3 words - "trial and error" :)
it was a question of trying various permutations of the syntax, using square brackets and/or ` delimiters. More luck than judgment though!Rubbish, you just know where ALL the Excel bodies are buried, that takes patience and wisdom, don't do your self down (that's our job :) ).

[rory]

Not all of them, by any means... :)

[agarwaldvk]

Dear Rory

I am sorry to bring this one up again but I tried something yesterday and I got some unexpected results - or was it that I got what I was supposed to get as the output but wasn't aware?

When I used the named range as you suggested, then regardless of the range that I spelt out for example "c8:e500" in the second square brackets, the recordset always contained as many records and as many fields as there were the number of rows and columns respectiviely in the 'UsedRange' on that worksheet.

Or am I doing it wrong?

Is it that the contents of the second square brackets is supposed to be the "name" of the named range with the named range having been previously defined?


Appreciate any help here in advance.

Best regards


Deepak

[rory]

You've lost me - you either use a named range, or a range address. If you use a named range, you can use:
strQUery = "SELECT * FROM [AdjFacOtherValues1 - Years1And2$blah];"
where 'blah' is the name of the range. Otherwise use the last syntax I posted to use a specific address.