Can we name a sheet without $?

  • Thread starter Thread starter Shalin
  • Start date Start date
S

Shalin

Hi,

I am facing problem with SQL whenever I extract
SheetName from the Excel Workbook. By default, Excel
appends $ to the sheet name. I don't want this to happen
then is there any way to do that?

Please let me know as quickly as possible.

Thanks,

Shalin
 
The $ tells you it is a sheet name e.g.

[Table1$]

If the name has no $ it means it is a Defined Name ('named range')
which was defined at the workbook level e.g.

[MyRange]

In Excel, Names can also be defined at the worksheet level and can be
accessed by prefixing with the sheet name e.g.

[Table1$MyRange2]

In answer to your question, the $ has a specific meaning. If you don't
want it, parse the string to remove it e.g.

If Right$(strTable) = "$" Then
strTable = Left$(strTable, Len(strTable) - 1)
End If
 
Actually I want to rename a sheet without $ and don't want
to parse it or anything just simply change the name that
contains no $.
-----Original Message-----
The $ tells you it is a sheet name e.g.

[Table1$]

If the name has no $ it means it is a Defined Name ('named range')
which was defined at the workbook level e.g.

[MyRange]

In Excel, Names can also be defined at the worksheet level and can be
accessed by prefixing with the sheet name e.g.

[Table1$MyRange2]

In answer to your question, the $ has a specific meaning. If you don't
want it, parse the string to remove it e.g.

If Right$(strTable) = "$" Then
strTable = Left$(strTable, Len(strTable) - 1)
End If

--

"Shalin" <[email protected]> wrote in
message news: said:
Hi,

I am facing problem with SQL whenever I extract
SheetName from the Excel Workbook. By default, Excel
appends $ to the sheet name. I don't want this to happen
then is there any way to do that?

Please let me know as quickly as possible.

Thanks,

Shalin
Click to expand...
.
Click to expand...
 
As I said, the $ has a specific meaning, you can't take that meaning
away. If you did, how would you be able to distinguish between a sheet
and a workbook level Defined Name which had the same name? That gives
me an idea:

Public Function DefineSheetsAsNames( _
Optional ByVal ExcelWorkbook As Workbook) As Boolean
Dim wb As Workbook
Dim ws As Worksheet
If ExcelWorkbook Is Nothing Then
Set wb = ActiveWorkbook
End If
With wb
For Each ws In .Worksheets
.Names.Add ws.Name, _
ws.Range("A1").Resize(Rows.Count, Columns.Count)
Next
End With
DefineSheetsAsNames = True
End Function

--

Shalin said:
Actually I want to rename a sheet without $ and don't want
to parse it or anything just simply change the name that
contains no $.
-----Original Message-----
The $ tells you it is a sheet name e.g.

[Table1$]

If the name has no $ it means it is a Defined Name ('named range')
which was defined at the workbook level e.g.

[MyRange]

In Excel, Names can also be defined at the worksheet level and can be
accessed by prefixing with the sheet name e.g.

[Table1$MyRange2]

In answer to your question, the $ has a specific meaning. If you don't
want it, parse the string to remove it e.g.

If Right$(strTable) = "$" Then
strTable = Left$(strTable, Len(strTable) - 1)
End If

--

"Shalin" <[email protected]> wrote in
message news: said:
Hi,

I am facing problem with SQL whenever I extract
SheetName from the Excel Workbook. By default, Excel
appends $ to the sheet name. I don't want this to happen
then is there any way to do that?

Please let me know as quickly as possible.

Thanks,

Shalin
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top